IF formula string

[Taylor]

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")

 

[Bernard Liengme]

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

 

[Taylor]

Thanks, Bernard. The VLOOKUP is much easier!

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