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!

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

"Suzanne" wrote:

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!

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

"Mike H" wrote:

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

"Suzanne" wrote:

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!

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.

--
Thank you -- Suzanne.


"Mike H" wrote:

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

"Suzanne" wrote:

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!

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" wrote:

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.

--
Thank you -- Suzanne.


"Mike H" wrote:

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

"Suzanne" wrote:

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!

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


"Mike H" wrote:

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" wrote:

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.

--
Thank you -- Suzanne.


"Mike H" wrote:

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

"Suzanne" wrote:

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!

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

"Suzanne" wrote:

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.

--
Thank you -- Suzanne.


"Mike H" wrote:

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

"Suzanne" wrote:

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!

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
--
Thank you -- Suzanne.


"Mike H" wrote:

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" wrote:

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.

--
Thank you -- Suzanne.


"Mike H" wrote:

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

"Suzanne" wrote:

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!

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



"Suzanne" wrote:

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
--
Thank you -- Suzanne.


"Mike H" wrote:

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" wrote:

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.

--
Thank you -- Suzanne.


"Mike H" wrote:

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

"Suzanne" wrote:

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!