If Formula Question (Can I have more than one result?)

[Guest]

I've searched the forum for the answer to this question, and I know it can be
done, I just haven't done it in a long time.

I'm trying to create a conditional formula that will allow me to have more
than one result. This is for tracking bonuses for the salespeople in our
company. For example, I want to have a column with the salesperson's name,
then a column where I will input their sales figures. I'd like for the bonus
column to auto fill with the desired amount. For example, less than $1000
results in no bonus, between $1000 and $5000 is a $100 bonus, et cetera. Is
this possible?

 

[Guest]

Use VLOOKUP() with a small table.

 

[Guest]

Yes, by using nested IF statements. Assuming the sales figure is in A1, the
formula below will return 0 if <1000, 100 if between 1000 and 5000, and 200
if >5000

=IF(A1<1000, 0, IF(AND(A1>=1000,A1<=5000), 100, 200))

 

[David Biddulph]

But you can omit the AND(A1>=1000,... as you've already tested for the
contrary condition.
You can simplify to =IF(A1<1000, 0, IF(A1<=5000, 100, 200))