 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / July 2007Excel Function IF AND
Hi Not too good at maths, trying to produce a formula to calulate discounts. if a>0.25 and b>10 discount =Z if a>0.25 and b>49 discount =Y if a>1 and b>10 discount =X if a>1 and b>49 discount =W if b>499 Discount =V I Have "=IF(AND(C4>0.25,B5>10),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations? If you wanted to go the route of a nested IF statement, then this may work for you. =IF(C4>0.25,IF(B5>10,I15,IF(B5>49,"DiscountYCell",0)),IF(C4>1,IF(B5>10,"DiscountXCell",IF(B4>49,"DiscountWCell",0)),IF(C4>499,"DiscountVCell",0))) However, you may want to consider creating a table and using VLOOKUP. HTH, Paul > Hi Not too good at maths, trying to produce a formula to calulate > discounts. [quoted text clipped - 7 lines] > out > all the calulations? Hi Filled in details to test formula as below and it's not working any suggestion please =IF(C4>0.25,IF(B5>10,5,IF(B5>49,7.5,0)),IF(C4>1,IF(B5>10,10,IF(B4>49,15,0)),IF(C4>499,20,0))) not sure what a VLOOKUP is will search help Phil > If you wanted to go the route of a nested IF statement, then this may work > for you. [quoted text clipped - 17 lines] > > out > > all the calulations? How is it not working? What is it doing or not doing? Gives us an example of your data in C4 and B5 and what result you expect. Regards, Paul > Hi Filled in details to test formula as below and it's not working any > suggestion please? [quoted text clipped - 26 lines] >> > out >> > all the calulations? This is what I'd expect & in () what's happening, C4 = 0.25 & B5 = 10 answer = 0 (this works) C4 = 0.26 & B5 = 11 answer = 5 (this works) C4 = 0.26 & B5 = 50 answer = 7.5 (however I change data never goes above 5) C4 = 1.1 & B5 = 11 answer = 10 C4 = 1.1 & B5 = 50 answer = 15 B5 = 500 answer = 20 Phil > How is it not working? What is it doing or not doing? > Gives us an example of your data in C4 and B5 and what result you expect. [quoted text clipped - 32 lines] > >> > out > >> > all the calulations? Try this: =IF(B5>499,20,IF(C4>1,IF(B5>49,15,IF(B5>10,10,0)),IF(C4>0.25,IF(B5>49,7.5,IF(B5>10,5,0)),0))) HTH, Paul > This is what I'd expect & in () what's happening, > C4 = 0.25 & B5 = 10 answer = 0 (this works) [quoted text clipped - 44 lines] >> >> > out >> >> > all the calulations? =IF(AND(A4>0.25,B4>10),Z,IF(AND(A4>0.25,B4>49),Y,IF(AND(A4>1,B4>10),X,IF(AND(A4>1,B4>49),W,IF(B4>499,V,"Invalid")))))  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi Not too good at maths, trying to produce a formula to calulate > discounts. [quoted text clipped - 7 lines] > out > all the calulations? Thanks for getting back so quickly, I've filled the formula as below to test and it's not working, any suggestion please. =IF(AND(C4>0.25,B5>10),5,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>1,B5>10),10,IF(AND(C4>1,B5>49),15,IF(B5>499,20,0))))) > =IF(AND(A4>0.25,B4>10),Z,IF(AND(A4>0.25,B4>49),Y,IF(AND(A4>1,B4>10),X,IF(AND(A4>1,B4>49),W,IF(B4>499,V,"Invalid"))))) > [quoted text clipped - 9 lines] > > out > > all the calulations? That is not a good reply, how can anyone help you when all you say is that it's not working. Surely you can't expect people to be mind readers? What did not work, did you get an error, was the result unexpected or did your computer blow up? SignatureRegards, Peo Sjoblom > Thanks for getting back so quickly, I've filled the formula as below to > test [quoted text clipped - 16 lines] >> > out >> > all the calulations? Hi Peo Sorry about the bad post, 1st time I've posted anything, should have thought it through before sending. Re: details see previous reply to PCLICVE, when I change my 2 variables, for the 1st 2 quantities the correct answer is return after that increasing the quantites does not increase the discount. It seems that this part of the formula is being acted on i.e."=IF(AND(C4>0.25,B5>10),I15,0)" then the calculation stops. Thanks all for the time you've spent on this, Phil > That is not a good reply, how can anyone help you when all you say is that > it's not working. [quoted text clipped - 21 lines] > >> > out > >> > all the calulations? No worries. Look at Dana's answer to see if it fits SignatureRegards, Peo Sjoblom > Hi Peo > Sorry about the bad post, 1st time I've posted anything, should have [quoted text clipped - 37 lines] >> >> > out >> >> > all the calulations? > I Have "=IF(AND(C4>0.25,B5>10),I15,0)" Hi. I believe the logic is a little off. If values pass the first test, it is possible they pass all the other tests. For example, if C4 =2, and B5=600, then this meets every test, especially the first test, and not the last test as probably expected. > if a>0.25 and b>10 discount =Z > if a>0.25 and b>49 discount =Y > if a>1 and b>10 discount =X > if a>1 and b>49 discount =W > if b>499... Usually, one works from highest to lowest. =IF(B1>499,20,IF(AND(A1>1,B1>49),15,IF(AND(A1>1,B1>10),10,IF(AND(A1>0.25,B1>49),7.5,IF(AND(A1>0.25,B1>10),5,0))))) SignatureHTH :>) Dana DeLouis > Hi Not too good at maths, trying to produce a formula to calulate > discounts. [quoted text clipped - 7 lines] > out > all the calulations? Thanks Dana That works really well, easy when you know how. all the best Phil > > I Have "=IF(AND(C4>0.25,B5>10),I15,0)" > [quoted text clipped - 23 lines] > > out > > all the calulations? I think my formula in the abve post was shorter...but they seem to accomplish the same result. > Thanks Dana > That works really well, easy when you know how. [quoted text clipped - 32 lines] >> > out >> > all the calulations? Another interesting option one can sometimes use is the following: Since both A1 & B1 can take on 3 states, we think of the combinations as a base-3 number. Excel's bug in Mod() prevents us from including B1>499, so we have to factor that out. I din't run another program to see if the numbers could be smaller. =IF(B1>499,20,MOD(28793600,15*(3*((B1>10)+(B1>49))+(A1>0.25)+(A1>1))+10)/2) SignatureDana DeLouis >I think my formula in the abve post was shorter...but they seem to >accomplish the same result. [quoted text clipped - 36 lines] >>> > out >>> > all the calulations? Hi Paul Yes your right, sorry there were so many post coming in I just missed yours. I've tested it & it works & is shorter, thanks for all your help. All the best Phil > I think my formula in the abve post was shorter...but they seem to > accomplish the same result. [quoted text clipped - 35 lines] > >> > out > >> > all the calulations? There are two solutions: =IF(B5>499,20,IF(AND(C4>1,B5>49),25,IF(AND(C4>1,B5>10),10,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>0.25,B5>10),5,0))))) and =IF(AND(C4>1,B5>49),25,IF(AND(C4>1,B5>10),10,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>0.25,B5>10),5,IF(B5>499,20,0))))) depending if B5>499 should be tested before or after then and conditions. The reason the other solutions failed is because they did the comparisons in the wrong sequence: if a1 is 10.: =IF(A1>2,5,IF(A1>5,6,0)) the condition a>2 is true, so 5 is returned and the condition a1>6 is never tested. The conditions have to be done in the reverse sequence =IF(A1>5,6,IF(A1>2,5,0)) > Hi Not too good at maths, trying to produce a formula to calulate > discounts. [quoted text clipped - 7 lines] > out > all the calulations? There are two solutions: =IF(B5>499,20,IF(AND(C4>1,B5>49),25,IF(AND(C4>1,B5>10),10,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>0.25,B5>10),5,0))))) and =IF(AND(C4>1,B5>49),25,IF(AND(C4>1,B5>10),10,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>0.25,B5>10),5,IF(B5>499,20,0))))) depending if B5>499 should be tested before or after the "and" conditions. The reason the other solutions failed is because they did the comparisons in the wrong sequence: if a1 is 10.: =IF(A1>2,5,IF(A1>5,6,0)) the condition a>2 is true, so 5 is returned and the condition a1>5 is never tested. The conditions have to be done in the reverse sequence =IF(A1>5,6,IF(A1>2,5,0)) > Hi Not too good at maths, trying to produce a formula to calulate > discounts. [quoted text clipped - 7 lines] > out > all the calulations? > There are two solutions: > [quoted text clipped - 22 lines] >> out >> all the calulations? Hi Thank's Dave I'll test both & see what answers I get. All the best Phil > There are two solutions: > [quoted text clipped - 49 lines] > >> out > >> all the calulations? A question, why do you keep posting multiple answers to questions posted here? Is there something wrong with your connection?. You posted the exact same answer a couple of minutes earlier? Another day you posted the same answer 4 times! I can imagine this happening once because you hit the send button twice. You obviously know Excel so one would expect that you would know how to use a newsreader as well. SignatureRegards, Peo Sjoblom > There are two solutions: > [quoted text clipped - 52 lines] >>> out >>> all the calulations? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.