Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 14th 05, 03:07 AM posted to microsoft.public.excel.worksheet.functions
johnsoned
 
Posts: n/a
Default 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.

  #2   Report Post  
Old December 14th 05, 04:29 AM posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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.



  #3   Report Post  
Old December 15th 05, 01:25 AM posted to microsoft.public.excel.worksheet.functions
johnsoned
 
Posts: n/a
Default 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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
making multiple columns when printing long datasheet Piet Excel Discussion (Misc queries) 1 November 11th 05 05:00 AM
Justify text across multiple columns fins2r Excel Discussion (Misc queries) 4 October 26th 05 05:07 PM
How to have multiple columns in excel that will expand and colaps. columnhelp Excel Discussion (Misc queries) 1 April 5th 05 11:42 PM
Pivot Table combining multiple columns Pete Petersen Excel Discussion (Misc queries) 1 January 13th 05 08:56 PM
HOW TO MATCH MULTIPLE COLUMNS WITH OR WITHOUT GAPS IN eXCEL ? hims Excel Worksheet Functions 2 October 27th 04 07:03 PM


All times are GMT +1. The time now is 07:39 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017