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. |
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. |
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. |
All times are GMT +1. The time now is 12:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com