Excel Formula Help

Brent Guttmann
Brent Guttmann used Ask the Experts™
on
I’m trying to create a formula for Column “D” that will tell me the percentage of Column “C” based on the range the number in Column “C” is.
 
For example: If greater than 0 and less than 2500 = 8%, If greater than 2500 and less than 5000 = 10%, If greater than 5000 and less than 10000 = 12%, etc., etc.
 
1      A      B      C      D
2      TEXT      TEXT      NUMBER      FORMULA
3      TEXT      TEXT      NUMBER      FORMULA
4      TEXT      TEXT      NUMBER      FORMULA
5      TEXT      TEXT      NUMBER      FORMULA
6      TEXT      TEXT      NUMBER      FORMULA
 
I was able to get this formula that work for the “If greater than 0 and less than 2500 = 8%”
 
=IF(AND(C2>=0,C2<=2500),C2*0.8)
 
When I try to add the additional percentages it does not work even on the first piece:
 
=IF(AND(C2>=0,C2<=2500),C2*0.8),AND(C2>=2500,C2<=5000),C2*0.10),AND(C2>=5000,C2<=1000),C2*0.12)



Any help would be appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi Brent,

It would be helpful, if you attach a sample workbook.
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Try below
=IF(C2="","",IF(AND(C2>0,C2<=2500),(C2*0.08),IF(AND(C2>=2501,C2<=5000),(C2*0.1),IF(AND(C2>=5001,C2<=10000),(C2*0.12),(C2*0.14)))))
You were specifying in <=2500 in first criteria, >=2500 again in second Criteria

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial