Get a percentage of a cell value under several conditions

[sofas]

Hello
Find a formula to check the value of two cells in the following form

IF A1<=B1
With a value from 0 to 2000, Get 1 % of the cell value B1
In the case of 2001 to 5000, Get 2 % of the cell value B1
In the case of 5001 to 7000, Get 3 % of the cell value B1
In the case of 7001 to 10000, Get 5 % of the cell value B1

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D
1	500	5200		156
Sheet13

Cell Formulas
Range	Formula
D1	D1	=IF(A1<B1,B1*IFS(B1<=2000,1%,B1<=5000,2%,B1<=7000,3%,TRUE,5%),0)

Is there a chance B1 could exceed 10000? If so, it will get 5% also.

 

[sofas]

I'm not sure if it could be made empty.​

 

[Eric W]

I don't understand your comment? Do you want the default value (if A1 > B1) to be empty? If so, try:

=IF(A1<B1,B1*IFS(B1<=2000,1%,B1<=5000,2%,B1<=7000,3%,TRUE,5%),"")