Share via

Frequency function not including all data in the range

Anonymous
2022-01-28T09:32:15+00:00

Hi,

I'm using a frequency function to count the number of calls abandoned in a call centre and the amount of time taken to abandon the call. It's currently counting over 40,000 values but only half of these are actually returning in the result.

I've looked in the data set and all the values that are not being counted are formatted slightly differently - they are aligned to the left of the cell rather than the right, I've tried formatted these values as a time and manually aligning them to the right but no luck.

Also, if I delete one cell and re-enter the value it is then included in the frequency data. I've tried cutting every value not being counted and pasting it back in but that didn't work.

I hope I've explained myself OK.

Any help much appreciated.

Cheers

Josh

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2022-01-28T09:58:01+00:00

    Thanks, I've used Text to Columns which seems to have worked.

    Was this answer helpful?

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz365 38,191 Reputation points Volunteer Moderator
    2022-01-28T10:45:16+00:00

    Well done Josh ==> Marking this thread as solved

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-01-28T10:07:39+00:00

    Hi JoshKing-Cox,

    I go through the post carefully, it is possible that the time value are saved as text, so when comparing in Frequency function, these Text Time values are not matched so are not included in the range.

    You may create a new column, and use TIMEVALUE function (microsoft.com) to Returns the decimal number of the time represented by a text string, then format this cells as time format, and refer this column in Frequency function.

    If the issue persists, could you please share some screenshots on a sample of your data with us?

    Regards,

    Clark

    Was this answer helpful?

    0 comments
  3. Lz365 38,191 Reputation points Volunteer Moderator
    2022-01-28T09:45:58+00:00

    Hi Josh

    ...all the values that are not being counted are...aligned to the left of the cell + if I delete one cell and re-enter the value it is then included in the frequency data

    This clearly means the "excluded" values are Text values instead of Numbers, hence why FREQUENCY ignore them. Without seeing some of those Text values it's virtually impossible to guide you precisely on how to convert them to numbers

    If you can't fix the problem yourself please:

    • upload & share your workbook (i.e with OneDrive, GoogleDrive...) and one will look at a possible solution
    • mention which version of Excel you run + Windows or Mac

    Was this answer helpful?

    0 comments