Forum Discussion
averages, but excluding part of the sum
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.
It works! Just to double-confirm, I added another layer (Cost 3, making it Costs 4 in total). Thanks, Hans!
