Formula for weighted average with IF statement

[stevemkiidub]

Hey there

OK, this one kind of stumped me but I knew you guys would probably figure it out quickly.

I currently have this formula that does a nice weighted avg for a 4 week period and the IF adds a "" if there is data missing so everything loosk really clean. In fact this forum did this exact formula!

=IF(OR(F23="",G23="",H23="",I23=""),"",F23*0.1+G23*0.2+H23*0.3+I23*0.4)

However, I thought it might be worth trying something different. Currently, if there are only 3 weeks of data but nothing for the 4th week, it just puts a blank (ie ""). But in reality, it would be probably a better idea that it just did a regular average of the 3 weeks of data vs putting no data at all (since this could throw off Planning if they notice a blank but in reality 3 weeks had some sales).

What do you think? Is it possible to make the formula capable of that?

Any help would be so appreciated as always!

[tim201110]

y mean that?

[stevemkiidub]

Oh, good eye. My issue is that the formula is taking data from Power Pivot and sometimes it shows a blank where there is no date. If it showed a 0 the formula I have would work fine. Hmmm. Not sure the solution here but I'll try messing around a bit. Any ideas?

[stevemkiidub]

Got it. It's a setting in Pivot table options. Now it shows a 0.

All is well! Thanks guys!!