Checking for a zero value in multiple columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

johnsoned - 14 Dec 2005 02:07 GMT

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.

Signature

Ed J.

Reply to this Message

Biff - 14 Dec 2005 03:29 GMT

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

>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
[quoted text clipped - 9 lines]
> to
> somebody out there.

Reply to this Message

johnsoned - 15 Dec 2005 00:25 GMT

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.

Signature

Ed J.

> Hi!
>
[quoted text clipped - 20 lines]
> > to
> > somebody out there.

Reply to this Message