| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - sort of
Can anybody help with a formula please
Data is in columns & I need to apply an inflation % to a value IF ColA=Yes & add up the values of each y/n bci end n 701 n 727 y 766 y 801 y 931 inflate 5% if(b2=y,c2*5%)+(if(b3=y,c3*5%)€¦etc So from above (766*5%)+(801*5%)+(931*5%) = 124.90 The column can grow & the 5% would need to be referenced Thanks in advance for any help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - sort of
HI
One way =SUMPRODUCT((A1:A100="y")*B1:B100)*5% -- Regards Roger Govier "Saintsman" wrote in message ... Can anybody help with a formula please Data is in columns & I need to apply an inflation % to a value IF ColA=Yes & add up the values of each y/n bci end n 701 n 727 y 766 y 801 y 931 inflate 5% if(b2=y,c2*5%)+(if(b3=y,c3*5%).etc So from above (766*5%)+(801*5%)+(931*5%) = 124.90 The column can grow & the 5% would need to be referenced Thanks in advance for any help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - sort of
Thanks to you both for this very quick reply!
Rogers solution solved it for me - albeit I needed to add -- before (A1:A100)...etc Thanks very much "Roger Govier" wrote: HI One way =SUMPRODUCT((A1:A100="y")*B1:B100)*5% -- Regards Roger Govier "Saintsman" wrote in message ... Can anybody help with a formula please Data is in columns & I need to apply an inflation % to a value IF ColA=Yes & add up the values of each y/n bci end n 701 n 727 y 766 y 801 y 931 inflate 5% if(b2=y,c2*5%)+(if(b3=y,c3*5%).etc So from above (766*5%)+(801*5%)+(931*5%) = 124.90 The column can grow & the 5% would need to be referenced Thanks in advance for any help |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - sort of
I'd try it again without the --.
The multiplication should be sufficient. Saintsman wrote: Thanks to you both for this very quick reply! Rogers solution solved it for me - albeit I needed to add -- before (A1:A100)...etc Thanks very much "Roger Govier" wrote: HI One way =SUMPRODUCT((A1:A100="y")*B1:B100)*5% -- Regards Roger Govier "Saintsman" wrote in message ... Can anybody help with a formula please Data is in columns & I need to apply an inflation % to a value IF ColA=Yes & add up the values of each y/n bci end n 701 n 727 y 766 y 801 y 931 inflate 5% if(b2=y,c2*5%)+(if(b3=y,c3*5%).etc So from above (766*5%)+(801*5%)+(931*5%) = 124.90 The column can grow & the 5% would need to be referenced Thanks in advance for any help -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - sort of
Doesn't need it, as the * will multiply all of the TRUEs by the equivalent
values in B1:B100 and return numeric values. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Saintsman" wrote in message ... Thanks to you both for this very quick reply! Rogers solution solved it for me - albeit I needed to add -- before (A1:A100)...etc Thanks very much "Roger Govier" wrote: HI One way =SUMPRODUCT((A1:A100="y")*B1:B100)*5% -- Regards Roger Govier "Saintsman" wrote in message ... Can anybody help with a formula please Data is in columns & I need to apply an inflation % to a value IF ColA=Yes & add up the values of each y/n bci end n 701 n 727 y 766 y 801 y 931 inflate 5% if(b2=y,c2*5%)+(if(b3=y,c3*5%).etc So from above (766*5%)+(801*5%)+(931*5%) = 124.90 The column can grow & the 5% would need to be referenced Thanks in advance for any help |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - sort of
Try this:
=SUMIF(A2:A6,"y",B2)*5% "Saintsman" wrote: Can anybody help with a formula please Data is in columns & I need to apply an inflation % to a value IF ColA=Yes & add up the values of each y/n bci end n 701 n 727 y 766 y 801 y 931 inflate 5% if(b2=y,c2*5%)+(if(b3=y,c3*5%)€¦etc So from above (766*5%)+(801*5%)+(931*5%) = 124.90 The column can grow & the 5% would need to be referenced Thanks in advance for any help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ascending Sort formula, change to neg #: descending sort.. | Excel Discussion (Misc queries) | |||
| sort function to sort for bell curve | Excel Discussion (Misc queries) | |||
| Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
| Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions | |||
| how can I hide sort ascending and sort descending options in the . | Excel Discussion (Misc queries) | |||
Hybrid Mode
