| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stratify Data In a Range
Hello, I am trying to stratify amounts in a given range of cells into
different tiers. I have a range of $ values. Lets say from A1:E100 that is filled with numerical values. I am trying to summarize this data into tiers, for example sum of amounts: from 10,000 to 15,000 sum of amounts: from 5,000 to 9,999 sum of amounts: from 0 to 4,999 sum of amounts: from (1) to (4,999) sum of amounts: from (5,000) to (9,999) sum of amounts: from (10,000) to (14,999) How do I do this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stratify Data In a Range
I tried it exactly how you wrote, but it tells me there is an error in the
formula. "Bernard Liengme" wrote: You want to know the SUM of the values in range X to Y In A105 enter 10,000; in B105 enter 15,000; in C105 enter =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105), $A$1:$E$100) If you just want a COUNT use =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105) In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106 et cetera For explainations see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Johnny Stranger" <Johnny wrote in message ... Hello, I am trying to stratify amounts in a given range of cells into different tiers. I have a range of $ values. Lets say from A1:E100 that is filled with numerical values. I am trying to summarize this data into tiers, for example sum of amounts: from 10,000 to 15,000 sum of amounts: from 5,000 to 9,999 sum of amounts: from 0 to 4,999 sum of amounts: from (1) to (4,999) sum of amounts: from (5,000) to (9,999) sum of amounts: from (10,000) to (14,999) How do I do this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stratify Data In a Range
typo, i should be
=SUMPRODUCT(--($A$1:$E$100=A105), --($A$1:$E$100<=B105), $A$1:$E$100) sorry -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Johnny Stranger" wrote in message ... I tried it exactly how you wrote, but it tells me there is an error in the formula. "Bernard Liengme" wrote: You want to know the SUM of the values in range X to Y In A105 enter 10,000; in B105 enter 15,000; in C105 enter =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105), $A$1:$E$100) If you just want a COUNT use =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105) In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106 et cetera For explainations see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Johnny Stranger" <Johnny wrote in message ... Hello, I am trying to stratify amounts in a given range of cells into different tiers. I have a range of $ values. Lets say from A1:E100 that is filled with numerical values. I am trying to summarize this data into tiers, for example sum of amounts: from 10,000 to 15,000 sum of amounts: from 5,000 to 9,999 sum of amounts: from 0 to 4,999 sum of amounts: from (1) to (4,999) sum of amounts: from (5,000) to (9,999) sum of amounts: from (10,000) to (14,999) How do I do this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stratify Data In a Range
I'm curious... what is the purpose of the "--" between the two opening
brackets you have in your formula?? I typed my formula: =SUMPRODUCT((Data=A11)*(Data<=B11),Data) with "Data" being the Range of cells, and it worked. "Bernard Liengme" wrote: typo, i should be =SUMPRODUCT(--($A$1:$E$100=A105), --($A$1:$E$100<=B105), $A$1:$E$100) sorry -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Johnny Stranger" wrote in message ... I tried it exactly how you wrote, but it tells me there is an error in the formula. "Bernard Liengme" wrote: You want to know the SUM of the values in range X to Y In A105 enter 10,000; in B105 enter 15,000; in C105 enter =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105), $A$1:$E$100) If you just want a COUNT use =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105) In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106 et cetera For explainations see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Johnny Stranger" <Johnny wrote in message ... Hello, I am trying to stratify amounts in a given range of cells into different tiers. I have a range of $ values. Lets say from A1:E100 that is filled with numerical values. I am trying to summarize this data into tiers, for example sum of amounts: from 10,000 to 15,000 sum of amounts: from 5,000 to 9,999 sum of amounts: from 0 to 4,999 sum of amounts: from (1) to (4,999) sum of amounts: from (5,000) to (9,999) sum of amounts: from (10,000) to (14,999) How do I do this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stratify Data In a Range
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Johnny Stranger wrote: I'm curious... what is the purpose of the "--" between the two opening brackets you have in your formula?? I typed my formula: =SUMPRODUCT((Data=A11)*(Data<=B11),Data) with "Data" being the Range of cells, and it worked. "Bernard Liengme" wrote: typo, i should be =SUMPRODUCT(--($A$1:$E$100=A105), --($A$1:$E$100<=B105), $A$1:$E$100) sorry -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Johnny Stranger" wrote in message ... I tried it exactly how you wrote, but it tells me there is an error in the formula. "Bernard Liengme" wrote: You want to know the SUM of the values in range X to Y In A105 enter 10,000; in B105 enter 15,000; in C105 enter =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105), $A$1:$E$100) If you just want a COUNT use =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105) In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106 et cetera For explainations see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Johnny Stranger" <Johnny wrote in message ... Hello, I am trying to stratify amounts in a given range of cells into different tiers. I have a range of $ values. Lets say from A1:E100 that is filled with numerical values. I am trying to summarize this data into tiers, for example sum of amounts: from 10,000 to 15,000 sum of amounts: from 5,000 to 9,999 sum of amounts: from 0 to 4,999 sum of amounts: from (1) to (4,999) sum of amounts: from (5,000) to (9,999) sum of amounts: from (10,000) to (14,999) How do I do this? -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Stratify Data In a Range
I got it working now... thanks. I don't know why it didn't work the first
time. "Bernard Liengme" wrote: You want to know the SUM of the values in range X to Y In A105 enter 10,000; in B105 enter 15,000; in C105 enter =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105), $A$1:$E$100) If you just want a COUNT use =SUMPRODUCT(--($A$1:$E$100)=A105, --($A$1:$E$100)<=B105) In A106 enter 5,000 and in B1006 enter 9,999 copy C105 to C106 et cetera For explainations see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Johnny Stranger" <Johnny wrote in message ... Hello, I am trying to stratify amounts in a given range of cells into different tiers. I have a range of $ values. Lets say from A1:E100 that is filled with numerical values. I am trying to summarize this data into tiers, for example sum of amounts: from 10,000 to 15,000 sum of amounts: from 5,000 to 9,999 sum of amounts: from 0 to 4,999 sum of amounts: from (1) to (4,999) sum of amounts: from (5,000) to (9,999) sum of amounts: from (10,000) to (14,999) How do I do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ranking query | Excel Discussion (Misc queries) | |||
| Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
| named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
| data range props | Excel Discussion (Misc queries) | |||
| data range | Excel Discussion (Misc queries) | |||
Hybrid Mode
