nested if with named formula

[Guest]

Hi. I need to be able to figure out the number of working days per month
based on 17 individual work calendars. I have used Chip Pearson's workaround
for more than 7 nested ifs and it works great. Problem is I have three
named formula and when I do a nested if using the names, it doesn't work.
Works fine if I only use two named fomula. Anyone have any ideas? Oh, the
boss doesn't want me to use code -- just formulas. Thanks in advance.

 

[Roger Govier]

Hi

You need to post some detail of what your data looks like, and the formula
you have used that doesn't work.

 

[Niek Otten]

Post your formula, input, expected and realized results

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi. I need to be able to figure out the number of working days per month
| based on 17 individual work calendars. I have used Chip Pearson's workaround
| for more than 7 nested ifs and it works great. Problem is I have three
| named formula and when I do a nested if using the names, it doesn't work.
| Works fine if I only use two named fomula. Anyone have any ideas? Oh, the
| boss doesn't want me to use code -- just formulas. Thanks in advance.

 

[Guest]

Here is the formula for the range named: Cal1

=IF(N2="July to June","7/1/2008",(IF(N2="Oct to
Sept","10/1/2008",(IF(N2="Jan - Dec","1/1/2008",(IF(N2="Apr to
Mar","4/1/2008",(IF(N2="Agency","10/1/2008",(IF(N2=Agency2,
"10/1/2008",FALSE)))))))))))

Here is the formula for the range named: Cal2

=IF(N2="Agency3","10/1/2008",(IF(N2="Agency4","10/1/2008",(IF(N2="Agency5","10/1/2008",(IF(N2="Agency6","10/1/2008",(IF(N2="Agency7","10/1/2008",(IF(N2="Agency8","10/1/2008",FALSE)))))))))))

Here is the formula for the range named: Cal3:

=IF(N2="Agency9","10/1/2008",(IF(N2="Agency10","10/1/2008",(IF(N2="Summer","7/1/2008",(IF(N2="Winter","7/1/2008",FALSE)))))))

Please note: N2 is where the end user indicates what calendar is applied
for a person for the upcoming fiscal year. Even though some of the calendars
may have the same start date (which is what gets return), the number of
working days may differ in each month depending on what the position is.

So I need to know what calendar is used to determine the number of days per
month to determine the salary, FICA, suta, other benefits, etc per month per
person.

I was hoping this would work but it doesn't:

=IF(Cal1=Cal1,Cal1,(IF(Cal2=Cal2,cal2)))

It will evaluate the first part of the IF only.

Hope I was clear.

 

[Roger Govier]

Hi

Create a Table with your various parameters in column A, and their
corresponding dates in column B. This can be on another sheet e.g Sheet2
Then use
=VLOOKUP(N2,Sheet2!$A:$B,2,0)

 

[Guest]

I was thinking about trying either VLookup or concantenating all 17 IFs.
I'll give them both a try. Thanks!