Forum Discussion
Solved
averages, but excluding part of the sum
Hi All,
I am attempting to get Month 4, based on averages of Months 1 to 3. However, in Month 4, one of the items has a fixed price and IS ALREADY part of Month 4 total.
What is the correct formula to get an average of Cost 1 and Cost 2, but still get to Month 4 total including Cost 3 (fixed)?
OK, thanks. I think the formula in E2 should be
=AVERAGE(B2:D2)/(AVERAGE(B$2:D$2)+AVERAGE(B$3:D$3))*(E$5-E$4)
Fill down to E3.
I'm afraid I don't understand what you're trying to do. Could you explain it in more detail?
HI Hans.
I am trying to get the proportionate Cost 1 and Cost 2 numbers, relevant to average based on Month 1 to 3, and of course the total. However, the amounts for Month 4 Cost 1 and 2 should consider Month 4 Cost 3, so that the total is still 230. Cost 3 is fixed at 55.
At the moment, the total for Month 4 is actually 285, and not 230. Again, Cost 3 is fixed and should be "untouched".OK, thanks. I think the formula in E2 should be
=AVERAGE(B2:D2)/(AVERAGE(B$2:D$2)+AVERAGE(B$3:D$3))*(E$5-E$4)
Fill down to E3.
