Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Loudmouth
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))
  #2   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

Hi!

The top formula has 7 nested levels which is the limit.

The bottom formula has 8.

How many conditions do you have in total?

Biff

"Loudmouth" wrote in message
...
This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))



  #3   Report Post  
Posted to microsoft.public.excel.misc
JLatham
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

As Biff has noted, the limit is 7 nested functions in a single formula. Most
people think this limit applies only to nested IF statements, but it is
actually a limit on the total number of nested functions of any type.

The second point to be made is that the limit is per formula. You could put
a + symbol at the end of your first formula and then repeat that same formula
and it would still work (although the result would be doubled) because each
individual formula only has 7 nested levels.

"Loudmouth" wrote:

This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))

  #4   Report Post  
Posted to microsoft.public.excel.misc
Loudmouth
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

I need about 25 to make it work. Can you suggest another way of comparing
that many numbers?

"Biff" wrote:

Hi!

The top formula has 7 nested levels which is the limit.

The bottom formula has 8.

How many conditions do you have in total?

Biff

"Loudmouth" wrote in message
...
This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))




  #5   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

I don't know exactly what you're doing but you need to create a table. This
table is based on your bottom formula:

0............0..........0
124.....0.45.....0.35
149.....0.55.....0.45
199.....0.7.......0.55
249.....0.9.......0.7
299.....1.05.....0.85
349.....1.25.....1
399.....1.4
499.....1.6

The 2nd column would be used when B40 and the third column would be used
when B4<=0. (at least, that's the logic of your IF formulas)

=IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3, 1))

Where D1:E9 is the above table.

Biff

"Loudmouth" wrote in message
...
I need about 25 to make it work. Can you suggest another way of comparing
that many numbers?

"Biff" wrote:

Hi!

The top formula has 7 nested levels which is the limit.

The bottom formula has 8.

How many conditions do you have in total?

Biff

"Loudmouth" wrote in message
...
This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))








  #6   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

This might be confusing:

=IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3 ,1))
Where D1:E9 is the above table.


Those table ranges, D1:E9 and D1:F7, are based on the unequal number of
entries in each column But I'm guessing that it's incomplete just because
you ran into nesting problems.

To make it less confusing just change it to: (and finish your table)

=IF(B40,VLOOKUP(H4,D1:F9,2,1),VLOOKUP(H4,D1:F9,3, 1))

Biff

"Biff" wrote in message
...
I don't know exactly what you're doing but you need to create a table. This
table is based on your bottom formula:

0............0..........0
124.....0.45.....0.35
149.....0.55.....0.45
199.....0.7.......0.55
249.....0.9.......0.7
299.....1.05.....0.85
349.....1.25.....1
399.....1.4
499.....1.6

The 2nd column would be used when B40 and the third column would be used
when B4<=0. (at least, that's the logic of your IF formulas)

=IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3, 1))

Where D1:E9 is the above table.

Biff

"Loudmouth" wrote in message
...
I need about 25 to make it work. Can you suggest another way of comparing
that many numbers?

"Biff" wrote:

Hi!

The top formula has 7 nested levels which is the limit.

The bottom formula has 8.

How many conditions do you have in total?

Biff

"Loudmouth" wrote in message
...
This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))







  #7   Report Post  
Posted to microsoft.public.excel.misc
JLatham
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

I think the easiest way to solve this problem is going to be for you to set
up a lookup table with your associated values in it and then use VLOOKUP()
inside of an IF() statement to pick your choices. This is difficult to
explain in words, so I prepared an example workbook which you can get at:
http://www.jlathamsite.com/uploads/v..._Loudmouth.xls

your formula in the cell where you're trying to build it now is going to
look something like
=IF(B40,VLOOKUP(H4,H9:I16,2,True),VLOOKUP(H4,H9:J 16,3,True))

The ranges mentioned, H9:I16 and H9:J16, will need to be changed to match
the range you set up in the real world.

A couple of things to note, the list must be in ascending order from top to
bottom based on your to-match values (399, 349, etc) in order for it to work
properly and reliably. The lookup matrix does not have to be on the same
sheet, you just have to be sure and reference the lookup range correctly.
For more information, look for VLOOKUP under Excel Help. I hope this helps
some.

"Loudmouth" wrote:

This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))

  #8   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

Or even...

=VLOOKUP(H4,D1:F9,if(b40,2,3),1)


Biff wrote:

This might be confusing:

=IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3 ,1))
Where D1:E9 is the above table.


Those table ranges, D1:E9 and D1:F7, are based on the unequal number of
entries in each column But I'm guessing that it's incomplete just because
you ran into nesting problems.

To make it less confusing just change it to: (and finish your table)

=IF(B40,VLOOKUP(H4,D1:F9,2,1),VLOOKUP(H4,D1:F9,3, 1))

Biff

"Biff" wrote in message
...
I don't know exactly what you're doing but you need to create a table. This
table is based on your bottom formula:

0............0..........0
124.....0.45.....0.35
149.....0.55.....0.45
199.....0.7.......0.55
249.....0.9.......0.7
299.....1.05.....0.85
349.....1.25.....1
399.....1.4
499.....1.6

The 2nd column would be used when B40 and the third column would be used
when B4<=0. (at least, that's the logic of your IF formulas)

=IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3, 1))

Where D1:E9 is the above table.

Biff

"Loudmouth" wrote in message
...
I need about 25 to make it work. Can you suggest another way of comparing
that many numbers?

"Biff" wrote:

Hi!

The top formula has 7 nested levels which is the limit.

The bottom formula has 8.

How many conditions do you have in total?

Biff

"Loudmouth" wrote in message
...
This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))






--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.misc
Loudmouth
 
Posts: n/a
Default How many concurrent nested IF statments does Excel allow?

Outstanding! Thank you very much. I am now able to complete my project.

"JLatham" wrote:

I think the easiest way to solve this problem is going to be for you to set
up a lookup table with your associated values in it and then use VLOOKUP()
inside of an IF() statement to pick your choices. This is difficult to
explain in words, so I prepared an example workbook which you can get at:
http://www.jlathamsite.com/uploads/v..._Loudmouth.xls

your formula in the cell where you're trying to build it now is going to
look something like
=IF(B40,VLOOKUP(H4,H9:I16,2,True),VLOOKUP(H4,H9:J 16,3,True))

The ranges mentioned, H9:I16 and H9:J16, will need to be changed to match
the range you set up in the real world.

A couple of things to note, the list must be in ascending order from top to
bottom based on your to-match values (399, 349, etc) in order for it to work
properly and reliably. The lookup matrix does not have to be on the same
sheet, you just have to be sure and reference the lookup range correctly.
For more information, look for VLOOKUP under Excel Help. I hope this helps
some.

"Loudmouth" wrote:

This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I generate the excel chart with out opening the excel instance? ramkumar_cpt Charts and Charting in Excel 1 March 1st 06 06:22 PM
Allow more than 7 nested IF THEN tests in EXCEL. Allow 30 or more R Excel Worksheet Functions 3 November 18th 05 07:38 AM
Do nested subtotals have an errror in Excel 2003 rlevitas Excel Discussion (Misc queries) 2 November 11th 05 07:46 PM
Excel nested IF formula question [email protected] Excel Discussion (Misc queries) 6 November 10th 05 06:11 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM


All times are GMT +1. The time now is 04:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"