Ask Experts Questions for FREE Help!
Ask    ||    Answer
 
Advanced  
 

Ask QuestionsprogressAnswer QuestionsprogressBuild ReputationprogressBecome an Expert
 
Free Answers in 3 Easy Steps

Register Now		 3 Steps

At Ask Me Help Desk you can ask questions in any topic and have them answered for free by our experts. To ask questions or participate in answering them you must register for a free account. By registering you will be able to:
  • Get free answers from experts in any of our 300+ topics.
  • Accept money for answers that you provide.
  • Communicate privately with other members (PM).
  • See fewer ads.

Home > Computers & Technology > Software > Spreadsheets   »   To much nesting in formula

Answer this Question Ask about Spreadsheets
 
Thread Tools Search this Thread Display Modes
Page 2 of 3 < 1 2 3 >
Question
 
 
Old Sep 16, 2009, 03:17 PM
Helpinlasvegas1
New Member
Helpinlasvegas1 is offline 		 
Join Date: Sep 2009
Posts: 10
Helpinlasvegas1 See this member's comment history on his/her Profile page.
To much nesting in formula
In excel I have 8 different categories for customer level, but to get to any one level there is three different categories that they must meet. I must have all 8 levels associated in one cell while at the same time evaluating the customers numbers to see if they qualify for any of the 8 levels. This is what I came up with =IF(AND(I6>325000,J6>575000,L6>250000),O6,”13”,IF( AND(I6>250000,J6>450000,L6>200000),O6,”12”,IF(AND( I6>200000,J6>350000,L6>150000),O6,”75000”,IF(AND(I 6>125000,J6>225000,L6>100000),O6,”50000”IF(AND(I6> 75000,J6>150000,L6>45000),O6,”30000”,IF(AND(I6>500 00,J6>100000,L6>30000),O6,”16000”,IF(AND(I6>25000, J6>40000,L6>10000),O6,”8000”,IF(AND(I6>10000,J6>20 000,L6>5000),O6,”3000”))))))))).

O6 is where I want the formula to be evaluated at and populate the correct # category.

Reply With Quote
 
     

Answers
 
 
Old Sep 19, 2009, 05:01 AM   #11  
Accounting Expert
ROLCAM is online now
 
Join Date: Dec 2005
Location: sydney australia
Posts: 1,012
ROLCAM See this member's comment history on his/her Profile page.
The formula is incorrect, I entered into
a cell of a spreadsheet and nothing happened.
Go back and see where you are going wrong.

Comments on this post
ScottGem disagrees: How can one found where they went wrong in a complex formula like this. Without explaining that, your answer is inaccurate.
  Reply With Quote
 
     
 
 
Old Sep 19, 2009, 05:35 AM   #12  
Computer Expert and Renaissance Man
ScottGem is offline
 
ScottGem's Avatar
 
Join Date: Jan 2003
Location: LI, NY - USA
Posts: 33,681
ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.ScottGem See this member's comment history on his/her Profile page.
Pay to call ScottGem for advice ($.75/min)
Call ScottGem via Skype™
If you want the result to appear in O6 the formula needs to be in O6. So that part of your formula is incorrect.

When building a complex IF statement like this, its best to build it up piecemeal. Build it one clause at a time, then combine them one at a time.

For example you start with (in O6):

=IF(AND(I6>325000,J6>575000,L6>250000,”13”,"False" )

Then create another IF in a separate cell for the False condition. If it works, then substitute that for "False" in O6. Then repeat the process for each condition to be tested.
  Reply With Quote
 
     
 
 
Old Sep 19, 2009, 03:36 PM   #13  
Software Expert
JBeaucaire is offline
 
JBeaucaire's Avatar
 
Join Date: Jan 2008
Location: (Call me JB) Bakersfield, CA
Posts: 4,699
JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.
Pay to call JBeaucaire for advice ($.75/min)
Call JBeaucaire via Skype™
Sorry you didn't notice that this was a duplicate thread to here:
http://www.askmehelpdesk.com/spreads...la-397026.html
Final solution is in post #5.
  Reply With Quote
 
     
 
 
Old Oct 14, 2009, 09:46 AM   #14  
New Member
Helpinlasvegas1 is offline
 
Join Date: Sep 2009
Posts: 10
Helpinlasvegas1 See this member's comment history on his/her Profile page.
"IF" statement in Excel returning False
I have an excel spreadsheet formula that works wonderfully, but I would like if the conditions have not been meet for it to read something other than "False". Is this possible?
  Reply With Quote
 
     
 
 
Old Oct 14, 2009, 09:57 AM   #15  
Engineering & Electronics Expert
KeepItSimpleStupid is offline
 
KeepItSimpleStupid's Avatar
 
Join Date: Aug 2007
Posts: 9,342
KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.
You mean somethiig like this:

=IF(2=3,5,"no way")

If 2=3, it returns "5" otherwise it returns "no way"
  Reply With Quote
 
     
 
 
Old Oct 14, 2009, 10:05 AM   #16  
New Member
Helpinlasvegas1 is offline
 
Join Date: Sep 2009
Posts: 10
Helpinlasvegas1 See this member's comment history on his/her Profile page.
The formula is already so long that when I tried to enter another if staement so there would be no false returns it was to much for excel
  Reply With Quote
 
     
 
 
Old Oct 14, 2009, 10:08 AM   #17  
New Member
Helpinlasvegas1 is offline
 
Join Date: Sep 2009
Posts: 10
Helpinlasvegas1 See this member's comment history on his/her Profile page.
"IF" statement in Excel returning False
=IF(G6>1250000,"Platinum",IF(G6>1000000,"Gold", IF(G6>750000,"Silver", IF(G6>500000,"*****", IF(G6>300000,"****", IF(G6>200000,"***", IF(G6>100000,"**", IF(G6>50000,"*"))))))))

This is my formula. In the end anything less than 50000 returns false. Excel will not allow me to add onto this formula to return a different " " answer. Is there any other way to change the outcome of "false" when the number is less than 50,000
  Reply With Quote
 
     
 
 
Old Oct 14, 2009, 10:58 AM   #18  
Software Expert
JBeaucaire is offline
 
JBeaucaire's Avatar
 
Join Date: Jan 2008
Location: (Call me JB) Bakersfield, CA
Posts: 4,699
JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.JBeaucaire See this member's comment history on his/her Profile page.
Pay to call JBeaucaire for advice ($.75/min)
Call JBeaucaire via Skype™
If you're evaluating the same cell over and over with multiple IF statements trying to match a particular value or text string, then IF statements are the wrong way. This is what LOOKUP and VLOOKUP and INDEX/MATCH is all about.

Post up your workbook and point out the issue, or at lease post up your current formula and desired additions. I'm sure we can offer something far more robust than IF/IF/IF/IF....
  Reply With Quote
 
     
 
 
Old Oct 14, 2009, 11:08 AM   #19  
Engineering & Electronics Expert
KeepItSimpleStupid is offline
 
KeepItSimpleStupid's Avatar
 
Join Date: Aug 2007
Posts: 9,342
KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.
helpinlasvegas. Keep the same topics togeter as threads.

Read "Answer this question" as "Reply to this question". It's the fault of the software makers.

I've asked that your threads be combined.
  Reply With Quote
 
     
 
 
Old Oct 14, 2009, 11:13 AM   #20  
Engineering & Electronics Expert
KeepItSimpleStupid is offline
 
KeepItSimpleStupid's Avatar
 
Join Date: Aug 2007
Posts: 9,342
KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.KeepItSimpleStupid See this member's comment history on his/her Profile page.
=IF(G6>1250000,"Platinum",IF(G6>1000000,"Gold", IF(G6>750000,"Silver", IF(G6>500000,"*****", IF(G6>300000,"****", IF(G6>200000,"***", IF(G6>100000,"**", IF(G6>50000,"*","no way"))))))))
  Reply With Quote
 
     

Your Answer
Email me when someone replies to my answer
Join Login



Answer this Question Ask about Spreadsheets
Page 2 of 3 < 1 2 3 >

« Previous Thread | Next Thread »

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

 
Similar Sponsors


Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page

Similar Threads
Levels of nesting in a formula
(12 replies)
Nesting If Function
(1 replies)
Nesting Syndrome
(2 replies)
Swan nesting
(0 replies)
Budgie nesting box
(3 replies)

Search this Thread

Advanced Search

Bookmarks

Sponsors



Copyright ©2003 - 2009, Ask Me Help Desk.
All times are GMT -8. The time now is 02:46 AM.