| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculating wtd number with conditional array formula
Hello, I have a data set that looks like this:
RATE UNITS 12.00% 0 0.00% 1500 10.00% 6000 20.00% 8000 and I need to calculate a weighted average rate. I need to exclude the row if either rate or units contain a 0 value. The result should show a wtd value of 15.71% my formula is a mess: ={(SUM(IF(AND(UNITS<0,RATES<0),(RATES*UNITS)/(SUM(IF(UNITS<0,UNITS,0))),0)))} If anyone can help straighten me out, I'd appreciate it. :) Thank you, Nacho |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating wtd number with conditional array formula
One way would be to create a third helper column in which you enter the
following formula (assumes your data starts in the 2nd row): =IF(A2*B2=0,"Ignore","Include" and fill down as necessary. Then you could filter out the "Ignore"s and run the weighted average. -- Brevity is the soul of wit. "nacholibre" wrote: Hello, I have a data set that looks like this: RATE UNITS 12.00% 0 0.00% 1500 10.00% 6000 20.00% 8000 and I need to calculate a weighted average rate. I need to exclude the row if either rate or units contain a 0 value. The result should show a wtd value of 15.71% my formula is a mess: ={(SUM(IF(AND(UNITS<0,RATES<0),(RATES*UNITS)/(SUM(IF(UNITS<0,UNITS,0))),0)))} If anyone can help straighten me out, I'd appreciate it. :) Thank you, Nacho |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating wtd number with conditional array formula
Thank you Dave - I was able to use your suggestion in this formula that
solved the problem! : =SUM(IF(ACFRATES*ACFUNITS<0,ACFRATES*ACFUNITS/(SUM(IF(ACFUNITS*ACFRATES<0,ACFUNITS,0))))) "Dave F" wrote: One way would be to create a third helper column in which you enter the following formula (assumes your data starts in the 2nd row): =IF(A2*B2=0,"Ignore","Include" and fill down as necessary. Then you could filter out the "Ignore"s and run the weighted average. -- Brevity is the soul of wit. "nacholibre" wrote: Hello, I have a data set that looks like this: RATE UNITS 12.00% 0 0.00% 1500 10.00% 6000 20.00% 8000 and I need to calculate a weighted average rate. I need to exclude the row if either rate or units contain a 0 value. The result should show a wtd value of 15.71% my formula is a mess: ={(SUM(IF(AND(UNITS<0,RATES<0),(RATES*UNITS)/(SUM(IF(UNITS<0,UNITS,0))),0)))} If anyone can help straighten me out, I'd appreciate it. :) Thank you, Nacho |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating wtd number with conditional array formula
Interesting. I take it that's an array formula?
-- Brevity is the soul of wit. "nacholibre" wrote: Thank you Dave - I was able to use your suggestion in this formula that solved the problem! : =SUM(IF(ACFRATES*ACFUNITS<0,ACFRATES*ACFUNITS/(SUM(IF(ACFUNITS*ACFRATES<0,ACFUNITS,0))))) "Dave F" wrote: One way would be to create a third helper column in which you enter the following formula (assumes your data starts in the 2nd row): =IF(A2*B2=0,"Ignore","Include" and fill down as necessary. Then you could filter out the "Ignore"s and run the weighted average. -- Brevity is the soul of wit. "nacholibre" wrote: Hello, I have a data set that looks like this: RATE UNITS 12.00% 0 0.00% 1500 10.00% 6000 20.00% 8000 and I need to calculate a weighted average rate. I need to exclude the row if either rate or units contain a 0 value. The result should show a wtd value of 15.71% my formula is a mess: ={(SUM(IF(AND(UNITS<0,RATES<0),(RATES*UNITS)/(SUM(IF(UNITS<0,UNITS,0))),0)))} If anyone can help straighten me out, I'd appreciate it. :) Thank you, Nacho |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating wtd number with conditional array formula
yep. :)
"Dave F" wrote: Interesting. I take it that's an array formula? -- Brevity is the soul of wit. "nacholibre" wrote: Thank you Dave - I was able to use your suggestion in this formula that solved the problem! : =SUM(IF(ACFRATES*ACFUNITS<0,ACFRATES*ACFUNITS/(SUM(IF(ACFUNITS*ACFRATES<0,ACFUNITS,0))))) "Dave F" wrote: One way would be to create a third helper column in which you enter the following formula (assumes your data starts in the 2nd row): =IF(A2*B2=0,"Ignore","Include" and fill down as necessary. Then you could filter out the "Ignore"s and run the weighted average. -- Brevity is the soul of wit. "nacholibre" wrote: Hello, I have a data set that looks like this: RATE UNITS 12.00% 0 0.00% 1500 10.00% 6000 20.00% 8000 and I need to calculate a weighted average rate. I need to exclude the row if either rate or units contain a 0 value. The result should show a wtd value of 15.71% my formula is a mess: ={(SUM(IF(AND(UNITS<0,RATES<0),(RATES*UNITS)/(SUM(IF(UNITS<0,UNITS,0))),0)))} If anyone can help straighten me out, I'd appreciate it. :) Thank you, Nacho |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula to count number of dates in an array | Excel Worksheet Functions | |||
Linear Mode
