Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula string
I'm having trouble creating a formula that performs the following function.
I want there to be a certain text output dependent upon which range bucket the value in cell T87 falls into. Each bucket clause works when entered individually, but when put together separated by commas, the output is #VALUE. Is there a way to set up the formula so that only the TRUE response is given and the FALSE responses are ignored? I've included the formula for reference. Thanks for any help! =IF(T87=10000,"6% Net 90"),IF(AND(T87=8000,T87<10000),"5% Net 90"), IF(AND(T87=5000,T87<8000),"4% Net 75"),IF(AND(T87=3000,T87<5000),"3% Net 75"), IF(AND(T87=2000,T87<3000),"2% Net 60"), IF(AND(T87=1000,T87<2000),"1% Net 60"),IF(T87<1000, "0% Net 30") |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula string
You might begin by simplifying this to a smaller formula
Let's look at a short version =IF(T87=10000,"6% Net 90"),IF(AND(T87=8000,T87<10000),"5% Net 90"), "No go") 1) For correct syntax this should be =IF(T87=10000,"6% Net 90",IF(AND(T87=8000,T87<10000),"5% Net 90", "No go") Notice I have remove two closing parentheses The syntax is =IF(test, true_value, false_value) with just a comma between true-value and false-value 2) It could be make simpler If T87 is great or equal to 10,000 , the true_value will always kick-in. There is no need to test that it is less than 10,000 - it has to be! =IF(T87=10000,"6% Net 90",IF(87=8000, "5% Net 90", "No go") Try this on a copy of your workbook to see if you understand. But there is an even better way - No IFs only VLOOKUP In any convenient place enter this data (I put it in A1:B7 of Sheet2) 0 0% Net 30 1000 1% Net 60 2000 2% Net 60 3000 3% Net 75 5000 4% Net 75 8000 5% Net 90 10000 6% Net 90 Then use =VLOOKUP(T87,Sheet2!A1:B7,2) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Taylor" wrote in message ... I'm having trouble creating a formula that performs the following function. I want there to be a certain text output dependent upon which range bucket the value in cell T87 falls into. Each bucket clause works when entered individually, but when put together separated by commas, the output is #VALUE. Is there a way to set up the formula so that only the TRUE response is given and the FALSE responses are ignored? I've included the formula for reference. Thanks for any help! =IF(T87=10000,"6% Net 90"),IF(AND(T87=8000,T87<10000),"5% Net 90"), IF(AND(T87=5000,T87<8000),"4% Net 75"),IF(AND(T87=3000,T87<5000),"3% Net 75"), IF(AND(T87=2000,T87<3000),"2% Net 60"), IF(AND(T87=1000,T87<2000),"1% Net 60"),IF(T87<1000, "0% Net 30") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula string
Thanks, Bernard. The VLOOKUP is much easier!
"Bernard Liengme" wrote: You might begin by simplifying this to a smaller formula Let's look at a short version =IF(T87=10000,"6% Net 90"),IF(AND(T87=8000,T87<10000),"5% Net 90"), "No go") 1) For correct syntax this should be =IF(T87=10000,"6% Net 90",IF(AND(T87=8000,T87<10000),"5% Net 90", "No go") Notice I have remove two closing parentheses The syntax is =IF(test, true_value, false_value) with just a comma between true-value and false-value 2) It could be make simpler If T87 is great or equal to 10,000 , the true_value will always kick-in. There is no need to test that it is less than 10,000 - it has to be! =IF(T87=10000,"6% Net 90",IF(87=8000, "5% Net 90", "No go") Try this on a copy of your workbook to see if you understand. But there is an even better way - No IFs only VLOOKUP In any convenient place enter this data (I put it in A1:B7 of Sheet2) 0 0% Net 30 1000 1% Net 60 2000 2% Net 60 3000 3% Net 75 5000 4% Net 75 8000 5% Net 90 10000 6% Net 90 Then use =VLOOKUP(T87,Sheet2!A1:B7,2) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Taylor" wrote in message ... I'm having trouble creating a formula that performs the following function. I want there to be a certain text output dependent upon which range bucket the value in cell T87 falls into. Each bucket clause works when entered individually, but when put together separated by commas, the output is #VALUE. Is there a way to set up the formula so that only the TRUE response is given and the FALSE responses are ignored? I've included the formula for reference. Thanks for any help! =IF(T87=10000,"6% Net 90"),IF(AND(T87=8000,T87<10000),"5% Net 90"), IF(AND(T87=5000,T87<8000),"4% Net 75"),IF(AND(T87=3000,T87<5000),"3% Net 75"), IF(AND(T87=2000,T87<3000),"2% Net 60"), IF(AND(T87=1000,T87<2000),"1% Net 60"),IF(T87<1000, "0% Net 30") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a Formula stored as String to real Formula | Excel Worksheet Functions | |||
formula for text string | Excel Worksheet Functions | |||
Formula to Replace or eliminate any sheetname(s) in formula string | Excel Discussion (Misc queries) | |||
formula with text string | Excel Discussion (Misc queries) | |||
Evaluate string as a formula | Excel Worksheet Functions |