IF Function Help due to 7 limit
 

Using Excel 2003

Using the following formula
It works great Except that when the input cell is blank ( or "deleted"), a
Zero
is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of 72,
so that part is looked after)

=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58<= 42,42,IF(W58<=48,48,IF(W58<=54,54,IF(W58<=60,60,IF (W58<=66,66,72))))))))

Thanks
--
John F. Scholten

If your min and max are covered by input restrictions then how about

=CEILING(W58,6)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"John F" wrote in message
...
Using Excel 2003

Using the following formula
It works great Except that when the input cell is blank ( or "deleted"),
a
Zero
is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of 72,
so that part is looked after)


=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58<= 42,42,IF(W58<=48,48,IF(W58
<=54,54,IF(W58<=60,60,IF(W58<=66,66,72))))))))

Thanks
--
John F. Scholten

If I understand your problem right, then you may be able to get away with
reversing the order in which you evaluate the source cell (in this case, W?).
Check from high-values to low-values, and throw a 0 as the false part for the
final test.

It may not be ideal, but try this:

=IF(W58=72,72,IF(W58=66,66,IF(W58=60,60,IF(W58 =58,58,IF(W58=54,54,IF(W58=48,48,IF(W58=42,42,I F(W58=36,36,IF(W58=30,30,IF(W58=24,24,0))))))))

"John F" wrote:

Using Excel 2003

Using the following formula
It works great Except that when the input cell is blank ( or "deleted"), a
Zero
is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of 72,
so that part is looked after)

=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58<= 42,42,IF(W58<=48,48,IF(W58<=54,54,IF(W58<=60,60,IF (W58<=66,66,72))))))))

Thanks
--
John F. Scholten

John F wrote...
....
It works great Except that when the input cell is blank ( or
"deleted"), a
Zero is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of
72,
so that part is looked after)

=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58< =42,42,IF(W58<=48,48,IF(W58<=54,54,
IF(W58<=60,60,IF(W58<=66,66,72))))))))

If you expect a formula like this to return 0 when W58 is 0, don't you
think you should have included 0 as a possible return value?

Your formula returns 24 only if W58 equals 24, but it returns the other
numbers when W58 falls into various ranges of numebrs. If you want it
to return 0 when W58 = 0 but 24 when W58 0 but < 24, then try

=IF(W58<=0,0,MIN(72,MAX(24,ROUNDUP(W58/6,0)*6)))

If you're mot always working with multiples of 6 (or any other number),
the general approach would be

=LOOKUP(B27-0.000001,{-1E+300;0;24;30;36;42;48;54;60;66},{0;24;30;36;42;4 8;54;60;66;72})

As I read the problem, if the value in W58 is between 0 and 23, it should
return a 0. If it's between 24 and 29, it should return a 24. If it's
between 30 and 35, it should return a 30. Etc....

Would the CEILING function do that?

"Ken Wright" wrote:

If your min and max are covered by input restrictions then how about

=CEILING(W58,6)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"John F" wrote in message
...
Using Excel 2003

Using the following formula
It works great Except that when the input cell is blank ( or "deleted"),
a
Zero
is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of 72,
so that part is looked after)


=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58<= 42,42,IF(W58<=48,48,IF(W58
<=54,54,IF(W58<=60,60,IF(W58<=66,66,72))))))))

Thanks
--
John F. Scholten

MDW wrote...
If I understand your problem right, then you may be able to get away
with
reversing the order in which you evaluate the source cell (in this
case, W?).
Check from high-values to low-values, and throw a 0 as the false part
for the
final test.

It may not be ideal, but try this:

=IF(W58=72,72,IF(W58=66,66,IF(W58=60,60,IF(W58 =58,58,IF(W58=54,54,IF(W58=48,48,
IF(W58=42,42,IF(W58=36,36,IF(W58=30,30,IF(W58 =24,24,0))))))))

"John F" wrote:
....
=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58 <=42,42,IF(W58<=48,48,IF(W58<=54,54,
IF(W58<=60,60,IF(W58<=66,66,72))))))))

Note the erroneous inclusion of W58=58.

OP's formula returns 30 when W58=27, 36 when W58=35, 72 when
W58=66.000000001, i.e., the multiples of 6 in the IF conditions are
*upper* ends of intervals that evaluate to their *upper* end values.
Your formula treats them as *lower* ends.

Testing is good.

You'd need to rewrite your formula as

=IF(W5866,72,IF(W5860,66,IF(W5854,60,IF(W5842, 48,IF(W5836,42,IF(W5830,36,
IF(W5824,30,IF(W58=24,24,0))))))))

Another new one, to me. - Works Great - Thanks much
John F.

"Ken Wright" wrote:

If your min and max are covered by input restrictions then how about

=CEILING(W58,6)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"John F" wrote in message
...
Using Excel 2003

Using the following formula
It works great Except that when the input cell is blank ( or "deleted"),
a
Zero
is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of 72,
so that part is looked after)


=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58<= 42,42,IF(W58<=48,48,IF(W58
<=54,54,IF(W58<=60,60,IF(W58<=66,66,72))))))))

Thanks
--
John F. Scholten

Thank-you
Are you working on a beta version? You have more nested than the standard
allowable 7 limit.
The =Ceiling did the trick
Thanks again

"MDW" wrote:

If I understand your problem right, then you may be able to get away with
reversing the order in which you evaluate the source cell (in this case, W?).
Check from high-values to low-values, and throw a 0 as the false part for the
final test.

It may not be ideal, but try this:

=IF(W58=72,72,IF(W58=66,66,IF(W58=60,60,IF(W58 =58,58,IF(W58=54,54,IF(W58=48,48,IF(W58=42,42,I F(W58=36,36,IF(W58=30,30,IF(W58=24,24,0))))))))

"John F" wrote:

Using Excel 2003

Using the following formula
It works great Except that when the input cell is blank ( or "deleted"), a
Zero
is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of 72,
so that part is looked after)

=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58<= 42,42,IF(W58<=48,48,IF(W58<=54,54,IF(W58<=60,60,IF (W58<=66,66,72))))))))

Thanks
--
John F. Scholten

Thank-you
Your =IF statement works great, due to the input validation restriction of
min. 24.

I don't understand the =Lookup statement.
Could I trouble you to explain it to me?
John F.

" wrote:

John F wrote...
....
It works great Except that when the input cell is blank ( or
"deleted"), a
Zero is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of
72,
so that part is looked after)

=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58< =42,42,IF(W58<=48,48,IF(W58<=54,54,
IF(W58<=60,60,IF(W58<=66,66,72))))))))

If you expect a formula like this to return 0 when W58 is 0, don't you
think you should have included 0 as a possible return value?

Your formula returns 24 only if W58 equals 24, but it returns the other
numbers when W58 falls into various ranges of numebrs. If you want it
to return 0 when W58 = 0 but 24 when W58 0 but < 24, then try

=IF(W58<=0,0,MIN(72,MAX(24,ROUNDUP(W58/6,0)*6)))

If you're mot always working with multiples of 6 (or any other number),
the general approach would be

=LOOKUP(B27-0.000001,{-1E+300;0;24;30;36;42;48;54;60;66},{0;24;30;36;42;4 8;54;60;66;72})

Would the CEILING function do that?

Nope, but the OP explicitly stated that the input parameters prevented any
entry outside the range 24-72 so all I need to do is cater for rounding up
to multiples of 6. There is no possibility of an entry between 0 and 23 in
the problem stated.

Also you have read the signs the wrong way as what he has said is if the
value is less than or equal to 30 for example, then round up to 30, not the
other way round.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"MDW" wrote in message
...
As I read the problem, if the value in W58 is between 0 and 23, it should
return a 0. If it's between 24 and 29, it should return a 24. If it's
between 30 and 35, it should return a 30. Etc....

Would the CEILING function do that?

"Ken Wright" wrote:

If your min and max are covered by input restrictions then how about

=CEILING(W58,6)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"John F" wrote in message
...
Using Excel 2003

Using the following formula
It works great Except that when the input cell is blank ( or
"deleted"),
a
Zero
is entered in cell "W?"
This in turn causes the formula to return a 30, NOT the Zero that I'm
Looking for.
Can the formula be reworked? or somehow multiple nested?
(the input/supply cell is restricted to minimum of 24 and maximum of
72,
so that part is looked after)



=IF(W58=24,24,IF(W58<=30,30,IF(W58<=36,36,IF(W58<= 42,42,IF(W58<=48,48,IF(W58
<=54,54,IF(W58<=60,60,IF(W58<=66,66,72))))))))

Thanks
--
John F. Scholten

John F wrote...
....
I don't understand the =Lookup statement.
Could I trouble you to explain it to me?
....
" wrote:
....
=LOOKUP(B27-0.000001,{-1E+300;0;24;30;36;42;48;54;60;66},{0;24;30;36;42;4 8;54;60;66;72})

Basically, LOOKUP with 3 arguments assumes its 2nd argument is sorted
in ascending order, locates the largest value in it less than or equal
to its 1st argument, and returns the corresponding value in the list
given as its 3rd argument. For example,

LOOKUP(2.5,{1;2;3},{10;100;1000})

would return 100 because the 2 in the 2nd argument is the largest value
less than or equal to 2.5, and 100 in the 3rd argument corresponds to 2
in the 2nd argument.

What you seem to want to do is a lookup into intervals. However, you
want to treat exact matches against your interval boundary points as
matching the *upper* end of your intervals rather than the lower end,
which is what LOOKUP does. By subtracting a small value from the 1st
argument, LOOKUP can be made to do what you want.

For example, in the topmost formula above, if B27 (sorry for not using
W58) were 30, then the 1st argument would evaluate to 29.999999. This
would match the 24 in the 2nd argument, and the corresponding value in
the 3rd argument would be 30. Note that the n_th entry in the 2nd
argument equals the (n-1)_th entry in the 3rd argument with the 1st
entry in the 2nd argument an extremely small number and the last entry
in the 3rd argument the highest allowed return value.

Thank you
I think I'm getting the hang of it.
I'll study your explanation carefully tomorrow morning.
Thanks Again for the xtra effort. It is appreciated.
John F.

" wrote:

John F wrote...
....
I don't understand the =Lookup statement.
Could I trouble you to explain it to me?
....
" wrote:
....
=LOOKUP(B27-0.000001,{-1E+300;0;24;30;36;42;48;54;60;66},{0;24;30;36;42;4 8;54;60;66;72})

Basically, LOOKUP with 3 arguments assumes its 2nd argument is sorted
in ascending order, locates the largest value in it less than or equal
to its 1st argument, and returns the corresponding value in the list
given as its 3rd argument. For example,

LOOKUP(2.5,{1;2;3},{10;100;1000})

would return 100 because the 2 in the 2nd argument is the largest value
less than or equal to 2.5, and 100 in the 3rd argument corresponds to 2
in the 2nd argument.

What you seem to want to do is a lookup into intervals. However, you
want to treat exact matches against your interval boundary points as
matching the *upper* end of your intervals rather than the lower end,
which is what LOOKUP does. By subtracting a small value from the 1st
argument, LOOKUP can be made to do what you want.

For example, in the topmost formula above, if B27 (sorry for not using
W58) were 30, then the 1st argument would evaluate to 29.999999. This
would match the 24 in the 2nd argument, and the corresponding value in
the 3rd argument would be 30. Note that the n_th entry in the 2nd
argument equals the (n-1)_th entry in the 3rd argument with the 1st
entry in the 2nd argument an extremely small number and the last entry
in the 3rd argument the highest allowed return value.