|
Create Group and Label Cell
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! |
Create Group and Label Cell
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! |
Create Group and Label Cell
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! |
Create Group and Label Cell
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! |
Create Group and Label Cell
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! |
Create Group and Label Cell
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! |
Create Group and Label Cell
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! |
Create Group and Label Cell
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! |
Create Group and Label Cell
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! |
| All times are GMT +1. The time now is 02:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com