Hello Rushiraj, There are many ways to work around that limitation, depending on the specific calculation task.
You already have several replies, but here are some other (and also similar)
methods.
Here are a few simple general ways:
Instead of:
=IF(what,then,IF(what2,then,IF(what3,..,otherwise))).
You can use (with numeric results):
=IF(what,then,0)+IF(what2,then,0)..
You can use (also with numeric results):
=(what)*(then)+(what2)*(then2)+..
You can use (with text results):
=IF(what,then,"")&IF(what2,then,"")..
And then you can use any of the lookup functions. They work in a pretty
straightforward manner with =, >= and <= criteria. The primary lookup
functions or function combinations are LOOKUP, VLOOKUP, HLOOKUP, INDEX/MATCH
and CHOOSE or CHOOSE/MATCH.
For your particular formula, besides the text IF version above (which would
work) here are some of the alternatives you could use in a single cell:
=IF(A3<126,"Below 126",LOOKUP(A3,{126,131,136,141,146,151,161,171,201},{"126
to 130","131 to 135","136 to 140","141 to 145","146 to 150","151 to
160","161 to 170","171 to 200",">200"}))
=IF(A3<126,"Below 126",HLOOKUP(A3,{126,131,136,141,146,151,161,171,201;"126
to 130","131 to 135","136 to 140","141 to 145","146 to 150","151 to
160","161 to 170","171 to 200",">200"},2))
=IF(A3<126,"Below 126",VLOOKUP(A3,{126,"126 to 130";131,"131 to
135";136,"136 to 140";141,"141 to 145";146,"146 to 150";151,"151 to
160";161,"161 to 170";171,"171 to 200";201,">200"},2))
=IF(A3<126,"Below
126",CHOOSE(MATCH(A3,{126,131,136,141,146,151,161,171,201}),"126 to
130","131 to 135","136 to 140","141 to 145","146 to 150","151 to 160","161
to 170","171 to 200",">200"))
=IF(A3<126,"Below 126",INDEX({"126 to 130";"131 to 135";"136 to 140";"141 to
145";"146 to 150";"151 to 160";"161 to 170";"171 to
200";">200"},MATCH(A3,{126,131,136,141,146,151,161,171,201})))
=CHOOSE(1+(A3>=126)+(A3>=131)+(A3>=136)+(A3>=141)+(A3>=146)+(A3>=151)+(A3>=1
61)+(A3>=171)+(A3>=201),"Below 126","126 to 130","131 to 135","136 to
140","141 to 145","146 to 150","151 to 160","161 to 170","171 to
200",">200")
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Rushiraj Patel
Sent: Tuesday, March 06, 2012 10:13 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ : How to use IF Function more than 8 times ?
Dear Experts,
I am using Excel 2003.
I have a file in which I need to bifurcate the figures in different slabs.
The Count of slabs goes up to 8. As per the Excel 2003 limitations I am only
able to get result till 7th slab and not more than that.
Please guide if there is any way out. Appended the reference files.
Rgds,
Rushiraj Patel
Save trees. Go Green.
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
<<image001.jpg>>
<<image002.jpg>>
