I have created a spreadsheet to track my investments. Each year is on a
separate tab in the worksheet. I would like to use a formula to calculate my
total gain/loss for the any given year including the current one. How do I
get Excel to check each month starting with the end of the year to the
beginning until it reaches a month that has a value and use that value in the
calculation. The formula =IF(O2=0,N2,O2)-C2 (where N2=end of Nov. total,
O2=end of Dec. total, C2=beginning of the year total) works for 2005 because
there is only one month to check for a zero value. I hope this makes sense to
somebody out there.
--
Ed J.

Hi!

Try this:

If O2 = December then D2 must = Jan, so C2 isn't "within" the range
criteria?

=IF(SUM(D2:O2),LOOKUP(2,1/(D2:O20),D2:O2)-C2,"")

Biff

"johnsoned" wrote in message
...
I have created a spreadsheet to track my investments. Each year is on a
separate tab in the worksheet. I would like to use a formula to calculate
my
total gain/loss for the any given year including the current one. How do I
get Excel to check each month starting with the end of the year to the
beginning until it reaches a month that has a value and use that value in
the
calculation. The formula =IF(O2=0,N2,O2)-C2 (where N2=end of Nov. total,
O2=end of Dec. total, C2=beginning of the year total) works for 2005
because
there is only one month to check for a zero value. I hope this makes sense
to
somebody out there.
--
Ed J.

Thanks for the prompt response to my question and for the formula. It worked
like a charm. Thank goodness for this discussion group. Keep up the good work.
--
Ed J.


"Biff" wrote:

Hi!

Try this:

If O2 = December then D2 must = Jan, so C2 isn't "within" the range
criteria?

=IF(SUM(D2:O2),LOOKUP(2,1/(D2:O20),D2:O2)-C2,"")

Biff

"johnsoned" wrote in message
...
I have created a spreadsheet to track my investments. Each year is on a
separate tab in the worksheet. I would like to use a formula to calculate
my
total gain/loss for the any given year including the current one. How do I
get Excel to check each month starting with the end of the year to the
beginning until it reaches a month that has a value and use that value in
the
calculation. The formula =IF(O2=0,N2,O2)-C2 (where N2=end of Nov. total,
O2=end of Dec. total, C2=beginning of the year total) works for 2005
because
there is only one month to check for a zero value. I hope this makes sense
to
somebody out there.
--
Ed J.