MrExcel Publishing
Your One Stop for Excel Tips & Solutions

need more than 7 nested


Posted by Judy on March 23, 2001 11:45 AM

What should I use if I need more than 7 nested functions? Just for example, if month = january, do something, february do something else, march and so on?

I am trying to keep up with employees monthly and ytd
actual and budget numbers.

Please give me a shove in the right direction!

Thanks


Posted by Aladin Akyurek on March 23, 2001 11:57 AM

Judy

It partly depends on all those 'somethings" that you want to do.
Care to provide a small snippet of your data, and an example of the formula with "7 nested functions"?

Aladin

Posted by mseyf on March 23, 2001 12:43 PM

you could use a CHOOSE and MATCH stmt instead of IF.
set up a range of 12 adjacent cells (like A1:A12)listing the 12 months, January, February, etc, and name the range something like MonthList. Name a range containing the month you wish to use something like ThisMonth. You could then use a formual like:

=CHOOSE(MATCH(ThisMonth,MontList,0),calc1,calc2,calc3,calc4,calc5,calc6,calc7,calc8,calc9,calc10,calc11,calc12)

substituting your formulas for January formula for calc1, February formula for calc2, etc.

HTH

Mark

Posted by mseyf on March 23, 2001 1:05 PM


Posted by Aladin Akyurek on March 23, 2001 1:06 PM

Mark: You must be on the right track. Cudos. -Aladin

Posted by Judy on March 23, 2001 2:24 PM

here is an example of the info I need


FEBRUARY
MONTH MONTH MONTH YTD
BUDGETED ACTUAL % CHARGED YTD % CHARGED
HOURS HOURS TO BUDGET OVER(UNDER) TO BUDGET

Employee #1 100 127.25 127.25% -31.5 93.70%
127.25/100 500-468.5 468.50/500
Employee #2

etc


EMPLOYEE #1
ACTUAL BUDGET
October 101.75 100
November 59 100
December 69.25 100
January 111.25 100
February 127.25 100
March
468.5 500