Average of a whole column of calculated values applied to individual cells without being able to pre-calculate the values to be averaged with conditio

[rriiicchh]

Good afternoon,

If you managed to understand the thread title then you probably already have the answer which is great news

If you didn't understand the title then hopefully my explanation below will help.

This problem would be trivial if I could have an extra column "Conversion" where I could pre-calculate and the conversion. However for reasons beyond my control I can't.

[TABLE="width: 500"]
<tbody>[TR]
[TD]In_Val[/TD]
[TD]Risk[/TD]
[TD]Pot_Val[/TD]
[TD]Completed[/TD]
[TD]Act_Val[/TD]
[TD]Conversion[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]Y[/TD]
[TD]1000[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]0.9[/TD]
[TD]1233[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]1[/TD]
[TD]730[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]Y[/TD]
[TD]1750[/TD]
[TD]0.88[/TD]
[/TR]
[TR]
[TD]3500[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]Y[/TD]
[TD]3000[/TD]
[TD]0.86[/TD]
[/TR]
</tbody>[/TABLE]

The formula for the field Pot_Val is where I am having issue.

In reality the formula should be

=if(Completed="Y",0,In_Val*Risk*Average(Conversion:Conversion))

where Conversion is =if(Completed="Y",Act_Val/In_Val,"")

The above table and formula is a simplified version of my overall table however it fully represents the needs.

Where I am having trouble is adding in the Average of Conversion and doing so in a way whereby I can easily apply this to every field in the Pot_Val column.

Hope that makes it clearer? Any help would be greatly appreciated.

 

[Andrew Poulsom]

Are you sure that you shouldn't be using a weighted average of conversion?

 

[rriiicchh]

Are you sure that you shouldn't be using a weighted average of conversion?

In short yes. In this situation there is no difference between a low "In_Val" and a large "In_Val" of achieving conversion.

 

[Andrew Poulsom]

In that case should it be (formula in C2 copied down)?

Excel 2010

	A	B	C	D	E
In_Val	Risk	Pot_Val	Completed	Act_Val
Y
N
N
Y
Y

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]1000[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]1194.23[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]707.692[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]1750[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]3000[/TD]

</TBODY>

Sheet8​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]C2[/TH]
[TD="align: left"]=IF(D2="Y",0,A2*B2*(SUMIF(D$1:D$6,"Y",E$1:E$6)/SUMIF(D$1:D$6,"Y",A$1:A$6)))[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[rriiicchh]

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]C2[/TH]
[TD="align: left"]=IF(D2="Y",0,A2*B2*(SUMIF(D$1:D$6,"Y",E$1:E$6)/SUMIF(D$1:D$6,"Y",A$1:A$6)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the suggestion however this averages the values rather than averages the conversion rate. In this example the "scaling factor" as calculated by this formula is 0.884 however what I would like it to be is 0.9107.

This is calculated by averaging 1000/1000, 1750/2000, 3000/3500.

Why this is important is because sometimes there can be large deviations which can causing the smearing to be totally off base.

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"></colgroup><tbody></tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]In_Val[/TD]
[TD]Risk[/TD]
[TD]Pot_Val_Required[/TD]
[TD]Pot_Val_AP_formula[/TD]
[TD]Completed[/TD]
[TD]Act_Val[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Y[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]0.9[/TD]
[TD]956[/TD]
[TD]142[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]1[/TD]
[TD]566[/TD]
[TD]84[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Y[/TD]
[TD]1750[/TD]
[/TR]
[TR]
[TD]3500[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Y[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]1000000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Y[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]100000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Looking at this example you can see the difference between what the suggested formula brings up and what I require. This difference is quite large. As the large value at the bottom skews the suggest formula to 0.105 rather than 0.708.

 

[Andrew Poulsom]

Sorry, I misunderstood your reply to my question and used a weighted average. Try:

=IF(D2="Y",0,A2*B2*(SUMPRODUCT(--(D$2:D$6="Y"),E$2:E$6/A$2:A$6)/COUNTIF(D$2:D$6,"Y")))

You may need to do some rounding.

 

[rriiicchh]

Sorry, I misunderstood your reply to my question and used a weighted average. Try:

=IF(D2="Y",0,A2*B2*(SUMPRODUCT(--(D$2:D$6="Y"),E$2:E$6/A$2:A$6)/COUNTIF(D$2:D$6,"Y")))

You may need to do some rounding.

THANKS! Very much appreciated!.

That does the trick perfectly. I have never used sumproduct before which looking at it now seems weird. I think it will get a lot of use from now on.

 

[rriiicchh]

I have just converted this formula to my spread sheet and have noticed an issue which is caused by a valid entry. Basically where the In_Val is 0 the formula returns #DIV/0! even though that value should be chucked out by having an "N" in the completed field. In the table below I have amended it to have this scenario in the first row. This "entry" appears many hundreds of times in the spread sheet and is a valid config unfortunately as I was hoping to correct the errors in the data to fix the issue.[TABLE="width: 378"]
<tbody>[TR]
[TD="class: xl63, width: 97, bgcolor: white"]In_Val
[/TD]
[TD="class: xl63, width: 97, bgcolor: white"]Risk
[/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Pot_Val
[/TD]
[TD="class: xl63, width: 97, bgcolor: white"]Completed
[/TD]
[TD="class: xl63, width: 97, bgcolor: white"]Act_Val
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 115, bgcolor: white"]#DIV/0!
[/TD]
[TD="class: xl63, width: 97, bgcolor: white"]N
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]1500
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"]0.9
[/TD]
[TD="class: xl64, width: 115, bgcolor: white"]#DIV/0!
[/TD]
[TD="class: xl63, width: 97, bgcolor: white"]N
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]800
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 115, bgcolor: white"]#DIV/0!
[/TD]
[TD="class: xl63, width: 97, bgcolor: white"]N
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]2000
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 115, bgcolor: white"]0
[/TD]
[TD="class: xl63, width: 97, bgcolor: white"]Y
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"]1750
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 97, bgcolor: white"]3500
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 115, bgcolor: white"]0
[/TD]
[TD="class: xl63, width: 97, bgcolor: white"]Y
[/TD]
[TD="class: xl64, width: 97, bgcolor: white"]3000
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

SUMPRODUCT evaluates all its arguments, so it will return an error if there is a zero in column A. Try this formula in C2:

=IF(D2="Y",0,A2*B2*(SUM(IF(D$2:D$6="Y",E$2:E$6/A$2:A$6))/COUNTIF(D$2:D$6,"Y")))

confirmed with Ctrl+Shift+Enter and copied down.