Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
I can not seem to get the syntax right.
=if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
You still haven't told us everything you need. And your use of quotes
doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
Hi,
The problem as stated does not make sense but probably this will start you in the correct direction: Make a little table like the one shown below, say in D1:E5 0 0-180 180 180-365 365 365-545 545 545-730 730 730 Enter the formula =VLOOKUP(A2,D1:E5,2,TRUE) If this helps, please click the Yes button Cheers, Shane Devenshire "jenn" wrote: I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
But of course the ANDs are unnecessary in that case. You don't need the
A2<730 case, because you've already tested for A2=730, and you don't need the A2<365 test because you've already tested for A2=365. -- David Biddulph "Fred Smith" wrote in message ... You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
=IF(A2=730,730,IF(A2545,A2<730,"545-730"),IF(A2=365,A2<545,"365-545",IF(A2<365,A2180,"180-365"),IF(A2<180,"<180"))))
Its supposed to look at the data in A2 and if it is greater than 730 display 730 if the number in A2 is between 545 and 730 display 545 - 730 etc I've gotten it to work before, but the above is telling me there are too many arguments and my nested experience is weak. thanks! "Fred Smith" wrote: You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
=IF(A2=730,730,IF(A2=545,"545-730",IF(A2=365,"365-545",IF(A2=180,"180-365","<180"))))
jenn wrote: =IF(A2=730,730,IF(A2545,A2<730,"545-730"),IF(A2=365,A2<545,"365-545",IF(A2<365,A2180,"180-365"),IF(A2<180,"<180")))) Its supposed to look at the data in A2 and if it is greater than 730 display 730 if the number in A2 is between 545 and 730 display 545 - 730 etc I've gotten it to work before, but the above is telling me there are too many arguments and my nested experience is weak. thanks! "Fred Smith" wrote: You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
It looks as if you haven't read the replies which Fred and I gave earlier?
Try changing your formula to =IF(A2=730,730,IF(A2545,"545-730",IF(A2=365,"365-545",IF(A2180,"180-365","<180")))) You may want to look again at whether you want or =, as you seem to have been inconsistent. The syntax of IF is very simple. It has 3 arguments, the first being a condition, the second is the outcome if the condition is true, and the third is the outcome if the condition is false (and the outcome defaults to a value of FALSE if you don't give that third argument). The nesting comes in if you decide that you want to replace one outcome or the other (or both) by a further formula. Remember that if the first test is satisfied, you don't go on to any tests which might be in the alternative (third argument) path, so you don't need to test again. Look at what I said in my reply earlier. -- David Biddulph "jenn" wrote in message ... =IF(A2=730,730,IF(A2545,A2<730,"545-730"),IF(A2=365,A2<545,"365-545",IF(A2<365,A2180,"180-365"),IF(A2<180,"<180")))) Its supposed to look at the data in A2 and if it is greater than 730 display 730 if the number in A2 is between 545 and 730 display 545 - 730 etc I've gotten it to work before, but the above is telling me there are too many arguments and my nested experience is weak. thanks! "Fred Smith" wrote: You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
ok, so I read it before... blew right past it in a huff... re read it... and
thought I understood... but my current formula returns "730" as the result for every line: =IF(A2=730,"730",IF(A2=545,"545-729",IF(A2365,"365-544",IF(A2180,"180-365",IF(A20,"<180"))))) "David Biddulph" wrote: It looks as if you haven't read the replies which Fred and I gave earlier? Try changing your formula to =IF(A2=730,730,IF(A2545,"545-730",IF(A2=365,"365-545",IF(A2180,"180-365","<180")))) You may want to look again at whether you want or =, as you seem to have been inconsistent. The syntax of IF is very simple. It has 3 arguments, the first being a condition, the second is the outcome if the condition is true, and the third is the outcome if the condition is false (and the outcome defaults to a value of FALSE if you don't give that third argument). The nesting comes in if you decide that you want to replace one outcome or the other (or both) by a further formula. Remember that if the first test is satisfied, you don't go on to any tests which might be in the alternative (third argument) path, so you don't need to test again. Look at what I said in my reply earlier. -- David Biddulph "jenn" wrote in message ... =IF(A2=730,730,IF(A2545,A2<730,"545-730"),IF(A2=365,A2<545,"365-545",IF(A2<365,A2180,"180-365"),IF(A2<180,"<180")))) Its supposed to look at the data in A2 and if it is greater than 730 display 730 if the number in A2 is between 545 and 730 display 545 - 730 etc I've gotten it to work before, but the above is telling me there are too many arguments and my nested experience is weak. thanks! "Fred Smith" wrote: You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
Check that your Tools / Options / Calculation is set to "Automatic".
jenn wrote: ok, so I read it before... blew right past it in a huff... re read it... and thought I understood... but my current formula returns "730" as the result for every line: =IF(A2=730,"730",IF(A2=545,"545-729",IF(A2365,"365-544",IF(A2180,"180-365",IF(A20,"<180"))))) "David Biddulph" wrote: It looks as if you haven't read the replies which Fred and I gave earlier? Try changing your formula to =IF(A2=730,730,IF(A2545,"545-730",IF(A2=365,"365-545",IF(A2180,"180-365","<180")))) You may want to look again at whether you want or =, as you seem to have been inconsistent. The syntax of IF is very simple. It has 3 arguments, the first being a condition, the second is the outcome if the condition is true, and the third is the outcome if the condition is false (and the outcome defaults to a value of FALSE if you don't give that third argument). The nesting comes in if you decide that you want to replace one outcome or the other (or both) by a further formula. Remember that if the first test is satisfied, you don't go on to any tests which might be in the alternative (third argument) path, so you don't need to test again. Look at what I said in my reply earlier. -- David Biddulph "jenn" wrote in message ... =IF(A2=730,730,IF(A2545,A2<730,"545-730"),IF(A2=365,A2<545,"365-545",IF(A2<365,A2180,"180-365"),IF(A2<180,"<180")))) Its supposed to look at the data in A2 and if it is greater than 730 display 730 if the number in A2 is between 545 and 730 display 545 - 730 etc I've gotten it to work before, but the above is telling me there are too many arguments and my nested experience is weak. thanks! "Fred Smith" wrote: You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
it is.
"Glenn" wrote: Check that your Tools / Options / Calculation is set to "Automatic". jenn wrote: ok, so I read it before... blew right past it in a huff... re read it... and thought I understood... but my current formula returns "730" as the result for every line: =IF(A2=730,"730",IF(A2=545,"545-729",IF(A2365,"365-544",IF(A2180,"180-365",IF(A20,"<180"))))) "David Biddulph" wrote: It looks as if you haven't read the replies which Fred and I gave earlier? Try changing your formula to =IF(A2=730,730,IF(A2545,"545-730",IF(A2=365,"365-545",IF(A2180,"180-365","<180")))) You may want to look again at whether you want or =, as you seem to have been inconsistent. The syntax of IF is very simple. It has 3 arguments, the first being a condition, the second is the outcome if the condition is true, and the third is the outcome if the condition is false (and the outcome defaults to a value of FALSE if you don't give that third argument). The nesting comes in if you decide that you want to replace one outcome or the other (or both) by a further formula. Remember that if the first test is satisfied, you don't go on to any tests which might be in the alternative (third argument) path, so you don't need to test again. Look at what I said in my reply earlier. -- David Biddulph "jenn" wrote in message ... =IF(A2=730,730,IF(A2545,A2<730,"545-730"),IF(A2=365,A2<545,"365-545",IF(A2<365,A2180,"180-365"),IF(A2<180,"<180")))) Its supposed to look at the data in A2 and if it is greater than 730 display 730 if the number in A2 is between 545 and 730 display 545 - 730 etc I've gotten it to work before, but the above is telling me there are too many arguments and my nested experience is weak. thanks! "Fred Smith" wrote: You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
If you are getting 730 for every line, one possibility is that you've got
text, rather than numbers, in the relevant cells. What do you get from the formulae =ISTEXT(A2) and =ISNUMBER(A2) ? If you have got text, you'll need to convert to numbers. You might get away with one or more of the following options: Copy a blank cell, then select your range of cells which need converting and Edit/ Paste Special/ Add Data/ Text to columns Look out for spaces, non-breaking spaces, or other non-printing characters and delete them. -- David Biddulph "jenn" wrote in message ... ok, so I read it before... blew right past it in a huff... re read it... and thought I understood... but my current formula returns "730" as the result for every line: =IF(A2=730,"730",IF(A2=545,"545-729",IF(A2365,"365-544",IF(A2180,"180-365",IF(A20,"<180"))))) "David Biddulph" wrote: It looks as if you haven't read the replies which Fred and I gave earlier? Try changing your formula to =IF(A2=730,730,IF(A2545,"545-730",IF(A2=365,"365-545",IF(A2180,"180-365","<180")))) You may want to look again at whether you want or =, as you seem to have been inconsistent. The syntax of IF is very simple. It has 3 arguments, the first being a condition, the second is the outcome if the condition is true, and the third is the outcome if the condition is false (and the outcome defaults to a value of FALSE if you don't give that third argument). The nesting comes in if you decide that you want to replace one outcome or the other (or both) by a further formula. Remember that if the first test is satisfied, you don't go on to any tests which might be in the alternative (third argument) path, so you don't need to test again. Look at what I said in my reply earlier. -- David Biddulph "jenn" wrote in message ... =IF(A2=730,730,IF(A2545,A2<730,"545-730"),IF(A2=365,A2<545,"365-545",IF(A2<365,A2180,"180-365"),IF(A2<180,"<180")))) Its supposed to look at the data in A2 and if it is greater than 730 display 730 if the number in A2 is between 545 and 730 display 545 - 730 etc I've gotten it to work before, but the above is telling me there are too many arguments and my nested experience is weak. thanks! "Fred Smith" wrote: You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested multiple if
kind sir..
thanks for your help... I did a very basic error and it's embarrassing, but I have to confess.... My data that I wanted to refer to is in the K column... not the A column has I used in my example... the formula is perfect... thank you VERY much. "David Biddulph" wrote: It looks as if you haven't read the replies which Fred and I gave earlier? Try changing your formula to =IF(A2=730,730,IF(A2545,"545-730",IF(A2=365,"365-545",IF(A2180,"180-365","<180")))) You may want to look again at whether you want or =, as you seem to have been inconsistent. The syntax of IF is very simple. It has 3 arguments, the first being a condition, the second is the outcome if the condition is true, and the third is the outcome if the condition is false (and the outcome defaults to a value of FALSE if you don't give that third argument). The nesting comes in if you decide that you want to replace one outcome or the other (or both) by a further formula. Remember that if the first test is satisfied, you don't go on to any tests which might be in the alternative (third argument) path, so you don't need to test again. Look at what I said in my reply earlier. -- David Biddulph "jenn" wrote in message ... =IF(A2=730,730,IF(A2545,A2<730,"545-730"),IF(A2=365,A2<545,"365-545",IF(A2<365,A2180,"180-365"),IF(A2<180,"<180")))) Its supposed to look at the data in A2 and if it is greater than 730 display 730 if the number in A2 is between 545 and 730 display 545 - 730 etc I've gotten it to work before, but the above is telling me there are too many arguments and my nested experience is weak. thanks! "Fred Smith" wrote: You still haven't told us everything you need. And your use of quotes doesn't make any sense -- ie, we can't tell if you want your results to be numbers of text. Finally, what do you want to happen when A2 is between 365 and 545? This syntax works, so maybe it will point you in the right direction: =if(a2=730,730,if(and(a2545,a2<730),a2,if(a2=36 5,365,if(and(a2<365,a2180),a2,0)))) Regards, Fred. "jenn" wrote in message ... I can not seem to get the syntax right. =if(AND((A2730,"730),(A2545,A2<730,"545-730"),(A2<365,A2<180,"180-365)))... basically... if 730 then 730 if between 545 and 730 then '545-730 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple nested If statements | Excel Worksheet Functions | |||
multiple (nested) IF statements | Excel Worksheet Functions | |||
Multiple (Nested) IF | Excel Discussion (Misc queries) | |||
multiple nested IF statements | Excel Worksheet Functions | |||
how do I use multiple nested functions? | Excel Worksheet Functions |