Excel nested IF

Microsoft Excel for mac 2001
September 23, 2010 at 17:52:16
Specs: Macintosh
I am trying create a spreadsheet that tracks various project financial performance over a three year period. There are a total of 8 types of projects (A-G and X) starting at various times over a three year period, for example Project A is month 1, B in 3, G in 4, A again in 5...etc. I am trying to maintain flexibility to change projects data (for # of projects and timing). I am using the following nested formula, but run out of capacity before finishing:

=IF(F$8<$D11,0,IF($C11="A",OFFSET('A Title'!$F$53,0,'Timing Table'!F$8-'Timing Table'!$D11),IF($C11="B",OFFSET('B Title'!$F$53,0,'Timing Table'!F$8-'Timing Table'!$D11),IF($C11="C",OFFSET('C Title'!$F$53,0,'Timing Table'!F$8-'Timing Table'!$D11),IF($C11="D",OFFSET('D Title'!$F$53,0,'Timing Table'!F$8-'Timing Table'!$D11),IF($C11="E",OFFSET('E Title'!$F$53,0,'Timing Table'!F$8-'Timing Table'!$D11),IF($C11="F",OFFSET('F Title'!$F$53,0,'Timing Table'!F$8-'Timing Table'!$D11),"NA")))))))

Where F$8 is the current month and $D11 is the month start date for a particular project. $C11 is the cell in which the project type is defined. I tried to define two formulas (AtoF and GtoX) and tried the formula (Iff$8<$d11,0,if(AtoF,AtoF,GtoX), but the inputs cells for each defined formula seemed to remain fixed and would not change across the time range.

Any help greatly appreciated

See More: Excel nested IF

Report •

September 23, 2010 at 17:53:54
I am using Microsoft Excel for Mac 2004, it that makes a difference

Report •

September 23, 2010 at 19:22:43
If I'm not mistaken, the only thing that changes is the first letter of the sheet names and you're checking for the first letter in C11 each time.

A Title
B Title

Why not eliminate all the IF that check C11 and use the text in C11 to build the sheet name via the INDIRECT function?

(For ease of testing, I used 1 as the cols argument for OFFSET. You, or course would use your Timing Table calculation.)

'A Title'!$F$53 becomes INDIRECT("'" & C11 &" Title'!$F$53"

Your entire formula becomes;

=IF(F$8<$D11, 0, OFFSET(INDIRECT("'" & C11 &" Title'!$F$53"), 0, 1))

This will take the value in C11 and construct a sheet name with it.

Look up the INDIRECT function in Excel help to see how it works. It basically takes a text argument and turns it into a cell reference.

Note the Double Quote - Single Quote - Double Quote at the beginning of the string.

The various parts of the text string must be enclosed in Double Quotes, including the Single Quote before the sheet name.

Report •

September 24, 2010 at 08:35:13
Worked perfectly and rather elegant construction. Thanks!

Report •

Related Solutions

September 24, 2010 at 08:58:57
Thanks! I kind of liked that one myself. ;-)

Report •

Ask Question