averaging formula

I currently have a formula which looks like this:

=AVERAGE(E9*D9,E10*D10,E11*D11,E12*D12,E13*D13,E14*D14,E15*D15,E16*D16,E17*D17,E18*D18)/AVERAGE(D9:D18)

Currently Cells E13,D13,E14,D14,E15,D15,E16,D16,E17,D17,E18,D18 are empty but would eventually become filled which is why i have included them in the average formula above. However it is obviously lowering the average because it is counting the cells that are empty as zero. I have been trying to use an IF FORMULA to correct this but as of yet have not managed it.

Am i pointing in the right direction, or perhaps someone could suggest an alternative formula.....thanks for your help!!!!

Hi,

Does this work for you

=SUM(D9:E18)/MAX(1,COUNT(D9:E18)-COUNTIF(D9:E18,0))/(SUM(D9:D18)/MAX(1,COUNT(D9:D18)-COUNTIF(D9:D18,0)))

try this

Please Login or Register  to view this content.

Hi,

Can you tell us what answer you are expecting?

Hi,

I've broken the formula down in the way you describe it and come up with 95.24% see attached, so I'm not sure who's correct?

Glad to hear it helped - thanks for the feedback