Calculate weighted average with missing values

Hi folks, hope somebody can help.

What I am trying to do is do a weighted calculation based on the results (values entered) for the three most recent months. The formula works on the basis of (Most recent Month *0.6) + (2nd most recent month * 0.2) + (3rd most recent month * 0.2)

However, each row will not have entries for each of the months. The attached sheet will explain further.

In Row 3 you will see entries(results) for May, April and March making the calculation straightforward. However, in Row 5 there is no entry (result) thus requiring that the calculation use the value for February.

How can I get the calculation (in row G) to account for missing values in some of the months.

Thanks

Rob

Maybe this?

Edit: NO, sorry, not good.. This take 3 greatest values.. Not 3 last values...

one possibility might be:

=SUM((MOD(LARGE(IF(ISNUMBER(B3:F3)*(B3:F3<>0),COLUMN(B3:F3)+(B3:F3/100000)),{1,2,3}),1)*100000)*$I$1:$K$1)
confirmed with CTRL + SHIFT + ENTER

though it's unclear as to your preference should have fewer than 3 valid data points

Originally Posted by zbor

However, this should be divided with 3 for average, right?

No I don't believe so - the % are being apportioned.

ie equiv. to having 60 entries of 50%, 20 entries of 40% and 20 entries of 30% - the Average of those 100 points would be 44% which is equiv. to SUM(0.5*0.6,0.4*0.2,0.3*0.2)

Originally Posted by Deiseman

Donkeyote, your formula in #3 gives me an answer in G3 of 0.00%, where it should read 44%

Note the text in bold red font underneath the suggested formula.

Based on your sample - the results would be:

Please Login or Register  to view this content.

In which case we simply remove the (B3:F3<>0) condition from the pre-emptive test:

=SUM((MOD(LARGE(IF(ISNUMBER(B3:F3),COLUMN(B3:F3)+(B3:F3/100000)),{1,2,3}),1)*100000)*$I$1:$K$1)
confirmed with CTRL + SHIFT + ENTER