Solved Excel Worksheet Calculation Help!

June 26, 2013 at 11:23:02
Specs: Windows 7
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?

See More: Excel Worksheet Calculation Help!

Report •

#1
June 26, 2013 at 15:19:59
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

http://www.skeptic.com/


Report •

#2
June 26, 2013 at 15:44:18
✔ Best Answer
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 is greater than 9999 then $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    800

Then 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.


Report •

#3
June 28, 2013 at 10:27:51
OMG! You're the best! It worked! And the good news....I understand it! Thank you so much for your help.

Report •
Related Solutions


Ask Question