Create Group and Label Cell

[Suzanne]

I have 14,000 rows of dollar amounts, in Sheet 1 column A, in Sheet 2 i have
groupings set up in $5,000 increments (similarly to frequency array set up).
Grouping column
4999
9999
14999
In Sheet 1 Colum B, i'd like to put a formula that will compare dollar
amount to grouping, fill grouping label in column B. I could set up Sheet 2
with 2 columns;
0 4999
5000 9999
10000 14999
to try and use some 'between' formula which i've yet to find.
End result
$6,869 9999

I must admit, this one has me stumped. Hope someone can assist. Thanks!

 

[Mike H]

Suzanne,

I'm not sure I fully understand the question but maybe this which will count
all $ amounts on sheet 1 in the range 0 to 5000

=COUNTIF(Sheet1!A1:A100,">0")-COUNTIF(Sheet1!A1:A100,">5000")


and for the next grouping
=COUNTIF(Sheet1!A1:A100,">=5000")-COUNTIF(Sheet1!A1:A100,">10000")


Mike

 

[Mike H]

Should have been

=COUNTIF(Sheet1!A1:A100,">0")-COUNTIF(Sheet1!A1:A100,">=5000")

and

=COUNTIF(Sheet1!A1:A100,">=5000")-COUNTIF(Sheet1!A1:A100,">=10000")

Mike

 

[Suzanne]

Thanks, i'm trying to solve with a single function or formula; my values
range from 0 to $80,000. I do appreciate your reply.

 

[Mike H]

Suzanne,

A formula or function can only change the cell it is in or is called from so
you can't do that with either method. You could resort to VB but that would
be overkill for counting. Try this. Create your grouping table on sheet 2 and
then use this formula

=COUNTIF(Sheet1!$A$1:$A$100,">="&B1)-COUNTIF(Sheet1!$A$1:$A$100,">="&C1)

Drag down as required

Col b Col C

0 4999
5000 9999
10000 14999

 

[Suzanne]

Now that sounds like a plan! Thanks i'll try it. Again, thanks.

 

[Tanya M]

I think a VLOOKUP formula should give you what you are looking for...

 

[Suzanne]

I'm trying to get the 'Bin' to fill the cell next to the value, (without
setting up a list of values from 0 - 70,000 (dollars and cents) for vlookup.

I'm looking for a formula to sit in cell next to my dollar amount and tell
me what bin it belongs to, as opposed to having a list of bins and counting
the amounts that fall in it. Such as, look at dollar amount and return the
bin label. vlook up would be great but i can't set up each one cent
increment.

Dollar amount Bin label
$ 5,123.54 $5,000 - $6,000

 

[Tanya M]

You don't have to set it up in one cent increments...

=vlookup(your dollar amt,table,column)

the table would look something like this:

0 first group
5000 second group
10000 third group
15000 fourth group