# Average formula to deal with blanks in sub calculations

1. ## Average formula to deal with blanks in sub calculations

Hello all,

Thank you for looking at this post!

I'm attempting to make a formula that will negate the need for multiple sub calculation formulas. I'm trying to wrap up a couple of calculations in one formula, but running into issues when trying to calculate blank cells. The average function (just looking at cells) ignores blanks and just uses the average of actual numbers provided, but when I try to make all of these calculations in one cell, the average function doesn't recognize "" as a blank, or to ignore. Take a look at the example and let me know if you can think of any ways to help deal with these blanks.

2. ## Re: Average formula to deal with blanks in sub calculations

I used =AVERAGE(C14:D21) and got 105.96%

3. ## Re: Average formula to deal with blanks in sub calculations

Originally Posted by FDibbins

I used =AVERAGE(C14:D21) and got 105.96%
Thanks for the reply. I'm attempting to make the formula without the use of those sub calculations. So creating a formula in D24 just using the table as reference. The sub calculations are really just for reference.

4. ## Re: Average formula to deal with blanks in sub calculations

I understand that, but what answer are you expecting?

5. ## Re: Average formula to deal with blanks in sub calculations

Hmm, C23 is average of derived value...

Should it be... (110+7.56+100+5)/(110+100) = 105.98%?

If above assumption is correct...
In D24: =SUMPRODUCT(((D3:D4<>"")*(\$B\$3:\$B\$4)+D3:D4))/SUMIF(D3:D4,"<>",\$B\$3:\$B\$4)

Copy to D23: You'll get 105.98%.

6. ## Re: Average formula to deal with blanks in sub calculations

Originally Posted by FDibbins
I understand that, but what answer are you expecting?
I'm just trying to get the same result as the first average function without using the sub calculations. So 105.94% in C24, and 107.27% in D24

7. ## Re: Average formula to deal with blanks in sub calculations

Originally Posted by CK76
Hmm, C23 is average of derived value...

Should it be... (110+7.56+100+5)/(110+100) = 105.98%?

If above assumption is correct...
In D24: =SUMPRODUCT(((D3:D4<>"")*(\$B\$3:\$B\$4)+D3:D4))/SUMIF(D3:D4,"<>",\$B\$3:\$B\$4)

Copy to D23: You'll get 105.98%.
Thanks for the help! This formula succeeds work finding the average of the first two Parts % of target, but doesn't work when trying to scale the formula to look at all the parts. Any idea what's wrong?

=AVERAGE(D14:D21) = 105.77%

=SUMPRODUCT(((D3:D10<>"")*(\$B\$3:\$B\$10)+D3:D10))/SUMIF(D3:D10,"<>",\$B\$3:\$B\$10) = 105.96%

8. ## Re: Average formula to deal with blanks in sub calculations

Because, as I explained in the post...

Average of individual derived value = AVERAGE(Calculation 1, Calculation 2, ....)

Is different from what I did. Which is calculating overall derived value for specified period.

Ex:
AVERAGE(106.87%,105.00%) = 105.94% (Average of... % of Target Part1 & 2)
(110+7.56+100+5)/(110+100) = 105.98% (Average % of target over Part1 & 2)

9. ## Re: Average formula to deal with blanks in sub calculations

As CK76 points out, it is mathematically incorrect to take an average of averages (a % is simply an average, portrayed as such)

There are currently 1 users browsing this thread. (0 members and 1 guests)