If a2 is less than $65000, 0, but if a2 is $65000 to $70000, $100, but if a2 is $70001 to $74999, $200, but if a2 is $75000 to $79999, $300, but if a2 is $80000 to $84999, $400, but if a2 is $85000 to $89999, $500, but if a2 is $90000 to $94999, $600, but if a2 is $95000 to $99999, $700, and if over $100000, $800 . I will manually enter the amount in 'a2'. How do I do this calculation in my excel worksheet?

Why did you start a whole new thread?

You could have just answered my query in the last one.Try this:

=IF(A2<65000,0,IF(A2<=70000,100,IF(A2<=74999,200,IF(A2<=79999,300,IF(A2<=84999,400,IF(A2<=89999,500,IF(A2<=94999,600,IF(A2<=99999,700,800))))))))

MIKE

First, I think you are missing a condition. "

...if a2 is $95000 to $99999, $700, and if over $100000, $800"What happens if A2 = 100000?

Assuming you meant "if A2 isgreater than 9999then $800" then Mike's IF statement will work.You could also use VLOOKUP.

Create a table a table that looks like this:

B C 1 64999 0 2 70000 100 3 70001 200 4 75000 300 5 80000 400 6 85000 500 7 90000 600 8 95000 700 9 100000 800Then use this formula:

=VLOOKUP(A2,$B$1:$C$9,2,1)

Use the Excel Help files (or Google) to learn how the VLOOKUP function works. It's very powerful. If you use VLOOKUP, you can change any of the parameters within the table itself and not have to edit a long Nested IF formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

OMG! You're the best! It worked! And the good news....I understand it! Thank you so much for your help.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History