Excel IF Statements

L

logistix2007

New Member
Joined
Nov 10, 2006
Messages
4
Hello all,

I've been asked to look at an Excel 2003 speadsheet which uses IF statements. Because the spreadsheet is relatively complex, it uses the maximum possible IF statements (totalling 7) per cell for Excel 2003. Apparently Excel 2007 supports 64 IF statements.

What methods can I use to get around this Excel 2003 limitation? Thanks.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
That depends heavily on what the IF's are supposed to achieve.
A lot can be done with for instance:
- conditional formats
- Sumproduct
- Sumif
- Countif
- Index / match
- lookup
- Vlookup
- Hlookup
- Pivottables
- autofilter
- advanced filter
- VBA Macros and UDF's (User Defined Functions).

Which method to use all depends on what the original builder of the spreadsheet was trying to do. If you need more than 7 nested IF's to get the results you want it is, in my opinion at least, very likely that the spreadsheet is poorly designed and might perform considerably better when re-designed from scratch.

However, without any further information it is practicly impossible to give you any concrete recommendations. There is no direct way I know of to break the 7 nested IF's Limit in 2003 apart from nesting 7 in a helper column and in another column nest as many other IF's s needed, based on the result in the helper column. WARNING - This is horribly inefficient and messy and not to be recommended.
 
Last edited:
Upvote 0
Here's a sample of the code I was using:

Code: 
=IF(C3<=17,"2a",IF(C3<=19,"3c",IF(C3<=21,"3b",IF(C3<=23,"3a",IF(C3<=25,"4c",IF(C3<=27,"4b",IF(C3<=29,"4a",IF(C3>=30,">5 or above",))))))))

I decided to use the Lookup function. I've gone upto 11 values and it appears to work:

Code: 
=LOOKUP(C3,{17,19,21,23,25,27,29,30,32,34,36},{"2a","3c","3b","3a","4c","4b","4a","5","5c","5b","5a"})

Many thanks for your prompt replies!
 
Upvote 0
Use "Select Case" instead of IFs

Code: 
Select Case Range("$A$1")
Case "xyz": Code to do something
Case "abc": Code to do something
'etc

lenze
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Conditional Formatting - How to Properly Copy + Paste without Duplicating Rules
Replies
1
Views
1K
6StringJazzer
6StringJazzer
JeffGrant
  • Solved
When is a workbook too big?
2
Replies
11
Views
1K
Michael M
Michael M
B
  • Question
Merging Excel Data into Word - Not working (it did before)
Replies
0
Views
184
Bud2014
B
A
  • Question
Excel table protection with formula copying
Replies
2
Views
567
Allie_Excel
A
M
  • Question
VBA code needed to extract cell info from a table and insert a variety of information into the body of an email
Replies
0
Views
559
MickyT7
M

Forum statistics

Threads
1,215,391
Messages
6,124,673
Members
449,179
Latest member
fcarfagna

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top