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
moving answer from one sheet to another 1
How to combine multiple columns into different cells in one column 4
Counting Ranges 2
Reporting from data into grouped ranges.. 1
Excel 2 into 1 6
How do I add more ranges to the following formula? 3
How do I set up a Dynamic Named Ranges in Excel 2007 7

Top