MrExcel Publishing
Your One Stop for Excel Tips & Solutions

weighted average

Posted by mcarter973 on January 30, 2002 3:36 PM

here's the example

a1 1,000 b1 200
a2 2,500 b2 100
a3 5,000 b3 0
a4 10,000 b4 0
a5 15,000 b5 0

if b1>0 then use a1
if b5>0 then use a5, but if b5=0 then don't use a4, use the last "a" value where the corresponding "b" value was >0.

i hope this makes sense.


Posted by IML on January 30, 2002 6:19 PM

Since it sounds like by default your B2 can't be zero (or if it is there is no number to grab), how about simply
=IF(B2,A2,C1) in cell c2 and copy it down?
this assumes C1 has the A1 value in it. You could put in something like
=A1*(B1<>0) in C1

If C1 fails the test you'll have zero's until one passes.

Posted by laura jean on February 05, 2002 7:28 AM, use c4 instead of a4 ;)