nested if with named formula
 

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.

nested if with named formula
 

Hi

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

--
Regards
Roger Govier



"SLP" wrote in message
...
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.

nested if with named formula
 

Post your formula, input, expected and realized results

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"SLP" wrote in message ...
| 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.

nested if with named formula
 

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.



"SLP" wrote:

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.

nested if with named formula
 

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)

--
Regards
Roger Govier



"SLP" wrote in message
...
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.



"SLP" wrote:

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.

nested if with named formula
 

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

"Roger Govier" wrote:

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)

--
Regards
Roger Govier



"SLP" wrote in message
...
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.



"SLP" wrote:

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.