Checking for a zero value in multiple columns

[johnsoned]

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.

[Biff]

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:O2>0),D2:O2)-C2,"")

Biff

"johnsoned" <[email protected]> wrote in message
news:[email protected]...
>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.

[johnsoned]

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:O2>0),D2:O2)-C2,"")
>
> Biff
>
> "johnsoned" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>