How do I reformat data into ranges

J

Jerry

I have an Exel file with 2 columns. The 1st column has over 40,000 different
values, the 2nd has only 5. I need to create ranges showing the Column A
values in ranges, i.e 1 thru 250=1. 251 thru 750=2. 750 thru 1250=1 etc. How
can this be done?
 
M

macropod

Hi Jerry,

You can use a series of COUNTIF formulae for this. For example:
=COUNTIF(A:A,"<250") returns the count of items less than or equal to 250
whilst
=COUNTIF(A:A,"<=250") returns the count of items less than 250
and
=COUNTIF(A:A,"<500")-COUNTIF(A:A,"<250") returns the count of items equal to or greater than 250 but less than 500
and
=COUNTIF(A:A,"<=500")-COUNTIF(A:A,">250") returns the count of items greater than 250 but less than or equal to 500.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I consolidate data into ranges 8
Mail Merge Help 1
moving answer from one sheet to another 1
Printing ranges 7
How to combine multiple columns into different cells in one column 5
Excel 2002: How to identify row of data ? 4
Append multiple columns 2
append multiple columns 2

Top