Remember Me? December 14th 05, 02:07 AM posted to microsoft.public.excel.worksheet.functions
 johnsoned Posts: n/a Checking for a zero value in multiple columns

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. December 14th 05, 03:29 AM posted to microsoft.public.excel.worksheet.functions
 Biff Posts: n/a Checking for a zero value in multiple columns

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. December 15th 05, 12:25 AM posted to microsoft.public.excel.worksheet.functions
 johnsoned Posts: n/a Checking for a zero value in multiple columns

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.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Piet Excel Discussion (Misc queries) 1 November 11th 05 04:00 AM fins2r Excel Discussion (Misc queries) 4 October 26th 05 05:07 PM columnhelp Excel Discussion (Misc queries) 1 April 5th 05 11:42 PM Pete Petersen Excel Discussion (Misc queries) 1 January 13th 05 07:56 PM hims Excel Worksheet Functions 2 October 27th 04 07:03 PM

All times are GMT +1. The time now is 09:15 AM. Copyright ©2004-2020 ExcelBanter.