| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Express a Multi IF Statement Q
How can I express the following in a formula
If B1 is greater than 4.5 but less than or = 5.9 AND B2 <0.15 THEN "Less than Min" + If B1 is greater = 4.5 AND B2 <0.15 THEN "Less than Min" + If B1 is greater than 4.5 but less than or = 5.9 AND B2 0.15 THEN "Blank" + If B1 is greater than 5.9 AND B2 <0.30 THEN "Less than Min" + Otherwise "Blank" Overall if B1 < 4.49 then it doesn't matter whats is B2, formula should return "Blank", and if B1 = 0, then "Blank" also regardless f whats in B2 I hope I've made it clear and covered all scenarios. The above should be in one cell hence the + I've used. I'm just getting confused trying to construct it Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Express a Multi IF Statement Q
Thanks Don
=IF(B15.9,IF(B20.3,"goodie", Up to above looks good but how do I continue with the formula by saying IF B1=4.5 BUT B1<5.9,IF(B2<0.15,"baddie","baddie")))) The bit thats catching me out is the "Between 4.5 and 5.9" The overall effect I'm trying to create is to check to see that if B1 is between 4.5 and 5.9 then B2 must be at least 0.15. If B2 is greater than 5.9 then B2 must be at least 0.30 Thanks "Don Guillett" wrote in message ... I didn't go thru the whole thing or test but this idea should help. Start at the TOP =IF(B15.9,IF(B20.3,"goodie",IF(B1=4.5,IF(B20.1 5,"Notsogoodie","baddie")))) -- Don Guillett SalesAid Software "John" wrote in message ... How can I express the following in a formula If B1 is greater than 4.5 but less than or = 5.9 AND B2 <0.15 THEN "Less than Min" + If B1 is greater = 4.5 AND B2 <0.15 THEN "Less than Min" + If B1 is greater than 4.5 but less than or = 5.9 AND B2 0.15 THEN "Blank" + If B1 is greater than 5.9 AND B2 <0.30 THEN "Less than Min" + Otherwise "Blank" Overall if B1 < 4.49 then it doesn't matter whats is B2, formula should return "Blank", and if B1 = 0, then "Blank" also regardless f whats in B2 I hope I've made it clear and covered all scenarios. The above should be in one cell hence the + I've used. I'm just getting confused trying to construct it Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Express a Multi IF Statement Q
try this idea this way to learn and then expand within. Start with higher
number. If c5 is higher than 5 it doesn't matter what the others are....... =if(c55,"a",if(c54,"b",if(c53,"c"))) then change the resulting "b" to the if within -- Don Guillett SalesAid Software "John" wrote in message ... Thanks Don =IF(B15.9,IF(B20.3,"goodie", Up to above looks good but how do I continue with the formula by saying IF B1=4.5 BUT B1<5.9,IF(B2<0.15,"baddie","baddie")))) The bit thats catching me out is the "Between 4.5 and 5.9" The overall effect I'm trying to create is to check to see that if B1 is between 4.5 and 5.9 then B2 must be at least 0.15. If B2 is greater than 5.9 then B2 must be at least 0.30 Thanks "Don Guillett" wrote in message ... I didn't go thru the whole thing or test but this idea should help. Start at the TOP =IF(B15.9,IF(B20.3,"goodie",IF(B1=4.5,IF(B20.1 5,"Notsogoodie","baddie")))) -- Don Guillett SalesAid Software "John" wrote in message ... How can I express the following in a formula If B1 is greater than 4.5 but less than or = 5.9 AND B2 <0.15 THEN "Less than Min" + If B1 is greater = 4.5 AND B2 <0.15 THEN "Less than Min" + If B1 is greater than 4.5 but less than or = 5.9 AND B2 0.15 THEN "Blank" + If B1 is greater than 5.9 AND B2 <0.30 THEN "Less than Min" + Otherwise "Blank" Overall if B1 < 4.49 then it doesn't matter whats is B2, formula should return "Blank", and if B1 = 0, then "Blank" also regardless f whats in B2 I hope I've made it clear and covered all scenarios. The above should be in one cell hence the + I've used. I'm just getting confused trying to construct it Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Express a Multi IF Statement Q
Don thanks for your lead
I compiled the following formula, its not quite correct =IF(B15.99,IF(C1=0.3,"OK"),IF(B14,IF(C1=0.15," OK"),"BAD")) I have the values 6 in B1 and 0.29 in C1. I wish to return "BAD", but I get a FALSE value returned. I guess I have a bracket in wrong or such like Thanks "Don Guillett" wrote in message ... try this idea this way to learn and then expand within. Start with higher number. If c5 is higher than 5 it doesn't matter what the others are....... =if(c55,"a",if(c54,"b",if(c53,"c"))) then change the resulting "b" to the if within -- Don Guillett SalesAid Software "John" wrote in message ... Thanks Don =IF(B15.9,IF(B20.3,"goodie", Up to above looks good but how do I continue with the formula by saying IF B1=4.5 BUT B1<5.9,IF(B2<0.15,"baddie","baddie")))) The bit thats catching me out is the "Between 4.5 and 5.9" The overall effect I'm trying to create is to check to see that if B1 is between 4.5 and 5.9 then B2 must be at least 0.15. If B2 is greater than 5.9 then B2 must be at least 0.30 Thanks "Don Guillett" wrote in message ... I didn't go thru the whole thing or test but this idea should help. Start at the TOP =IF(B15.9,IF(B20.3,"goodie",IF(B1=4.5,IF(B20.1 5,"Notsogoodie","baddie")))) -- Don Guillett SalesAid Software "John" wrote in message ... How can I express the following in a formula If B1 is greater than 4.5 but less than or = 5.9 AND B2 <0.15 THEN "Less than Min" + If B1 is greater = 4.5 AND B2 <0.15 THEN "Less than Min" + If B1 is greater than 4.5 but less than or = 5.9 AND B2 0.15 THEN "Blank" + If B1 is greater than 5.9 AND B2 <0.30 THEN "Less than Min" + Otherwise "Blank" Overall if B1 < 4.49 then it doesn't matter whats is B2, formula should return "Blank", and if B1 = 0, then "Blank" also regardless f whats in B2 I hope I've made it clear and covered all scenarios. The above should be in one cell hence the + I've used. I'm just getting confused trying to construct it Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Express a Multi IF Statement Q
Hi John
Maybe =IF(B15.99,IF(C1=0.3,"OK","BAD"), IF(B14,IF(C1=0.15,"OK","BAD"),"BAD")) -- Regards Roger Govier "John" wrote in message ... Don thanks for your lead I compiled the following formula, its not quite correct =IF(B15.99,IF(C1=0.3,"OK"),IF(B14,IF(C1=0.15," OK"),"BAD")) I have the values 6 in B1 and 0.29 in C1. I wish to return "BAD", but I get a FALSE value returned. I guess I have a bracket in wrong or such like Thanks "Don Guillett" wrote in message ... try this idea this way to learn and then expand within. Start with higher number. If c5 is higher than 5 it doesn't matter what the others are....... =if(c55,"a",if(c54,"b",if(c53,"c"))) then change the resulting "b" to the if within -- Don Guillett SalesAid Software "John" wrote in message ... Thanks Don =IF(B15.9,IF(B20.3,"goodie", Up to above looks good but how do I continue with the formula by saying IF B1=4.5 BUT B1<5.9,IF(B2<0.15,"baddie","baddie")))) The bit thats catching me out is the "Between 4.5 and 5.9" The overall effect I'm trying to create is to check to see that if B1 is between 4.5 and 5.9 then B2 must be at least 0.15. If B2 is greater than 5.9 then B2 must be at least 0.30 Thanks "Don Guillett" wrote in message ... I didn't go thru the whole thing or test but this idea should help. Start at the TOP =IF(B15.9,IF(B20.3,"goodie",IF(B1=4.5,IF(B20.1 5,"Notsogoodie","baddie")))) -- Don Guillett SalesAid Software "John" wrote in message ... How can I express the following in a formula If B1 is greater than 4.5 but less than or = 5.9 AND B2 <0.15 THEN "Less than Min" + If B1 is greater = 4.5 AND B2 <0.15 THEN "Less than Min" + If B1 is greater than 4.5 but less than or = 5.9 AND B2 0.15 THEN "Blank" + If B1 is greater than 5.9 AND B2 <0.30 THEN "Less than Min" + Otherwise "Blank" Overall if B1 < 4.49 then it doesn't matter whats is B2, formula should return "Blank", and if B1 = 0, then "Blank" also regardless f whats in B2 I hope I've made it clear and covered all scenarios. The above should be in one cell hence the + I've used. I'm just getting confused trying to construct it Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to Express a Multi IF Statement Q
Thanks Roger was able to tune it ever so slightly and obtained the right
result with the FALSE statements =IF(B1=(6/24),IF(C1=(30/1440),"OK","BAD"),IF(B1=(4.5/24),IF(C1=(15/1440),"OK","BAD"),IF(B1<(4.5/24),IF(C1=0,"OK","BAD"),"BAD"))) "Roger Govier" wrote in message ... Hi John Maybe =IF(B15.99,IF(C1=0.3,"OK","BAD"), IF(B14,IF(C1=0.15,"OK","BAD"),"BAD")) -- Regards Roger Govier "John" wrote in message ... Don thanks for your lead I compiled the following formula, its not quite correct =IF(B15.99,IF(C1=0.3,"OK"),IF(B14,IF(C1=0.15," OK"),"BAD")) I have the values 6 in B1 and 0.29 in C1. I wish to return "BAD", but I get a FALSE value returned. I guess I have a bracket in wrong or such like Thanks "Don Guillett" wrote in message ... try this idea this way to learn and then expand within. Start with higher number. If c5 is higher than 5 it doesn't matter what the others are....... =if(c55,"a",if(c54,"b",if(c53,"c"))) then change the resulting "b" to the if within -- Don Guillett SalesAid Software "John" wrote in message ... Thanks Don =IF(B15.9,IF(B20.3,"goodie", Up to above looks good but how do I continue with the formula by saying IF B1=4.5 BUT B1<5.9,IF(B2<0.15,"baddie","baddie")))) The bit thats catching me out is the "Between 4.5 and 5.9" The overall effect I'm trying to create is to check to see that if B1 is between 4.5 and 5.9 then B2 must be at least 0.15. If B2 is greater than 5.9 then B2 must be at least 0.30 Thanks "Don Guillett" wrote in message ... I didn't go thru the whole thing or test but this idea should help. Start at the TOP =IF(B15.9,IF(B20.3,"goodie",IF(B1=4.5,IF(B20.1 5,"Notsogoodie","baddie")))) -- Don Guillett SalesAid Software "John" wrote in message ... How can I express the following in a formula If B1 is greater than 4.5 but less than or = 5.9 AND B2 <0.15 THEN "Less than Min" + If B1 is greater = 4.5 AND B2 <0.15 THEN "Less than Min" + If B1 is greater than 4.5 but less than or = 5.9 AND B2 0.15 THEN "Blank" + If B1 is greater than 5.9 AND B2 <0.30 THEN "Less than Min" + Otherwise "Blank" Overall if B1 < 4.49 then it doesn't matter whats is B2, formula should return "Blank", and if B1 = 0, then "Blank" also regardless f whats in B2 I hope I've made it clear and covered all scenarios. The above should be in one cell hence the + I've used. I'm just getting confused trying to construct it Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If statement using multi columns | Excel Worksheet Functions | |||
| Anybody have a multi division income statement template? | Excel Discussion (Misc queries) | |||
| If statement | Excel Discussion (Misc queries) | |||
| Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
| How to express this in an if statement. | Excel Worksheet Functions | |||
Linear Mode
