# calculating percentage based on preset criteria

#### dakk

##### Board Regular
I would like to be able to calculate the percentage based on the amount of the value versus the preset contract. So for example if cell A7 is B and cell G7 is in in the range of 500000 to 749999 the percentage returned is 30 in cell J7. I would like to know if it possible to have a formula that would calculate on the following regardless of the cell:

Contract From To %returned
B <500000 0
B 500000----749999 30
B 750000----1000000 40
B >1000000 50
C 0-----------249999 10
C 250000----499999 20
C 500000----749999 35
C 750000----999999 45
C >1000000 60

Thank you in advance please let me know if this is unclear.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You could set up a table as shown below.....
Book1
ABCDEFGHIJKLMNOPQ
1valueBC
200%10%
32500000%20%
450000030%35%
575000040%45%
6100000050%60%
7B77777740%
8C121212160%
9A123456#N/A
10
Sheet3

Formula in J7 copied down column

=VLOOKUP(G7,\$N\$2:\$P\$6,MATCH(A7,\$N\$1:\$P\$1,0))

Hi,

You can set up your data as follows, starting in Column A, row 1. cell A1 will actually be blank but type B in B1 and C in C1.

0 B C
0 0 10
250000 0 20
500000 30 35
750000 40 45
1000001 50 65

If the letter representing the contract is in cell G6 and the dollar amount is in G7, type the following in G8 to calculate the return.

=G7*(VLOOKUP(G7,A:A,MATCH(G6,\$A\$1:\$C\$1))&"%")

Hope this helps.

Ron

With F2 (contract) B or C
G2 is the value
and H2 as the result

=IF(F2="b",LOOKUP(G2,{0,500000,750000,1000001},{0,30,40,50}),IF(F2="c",LOOKUP(G2,{0,250000,500000,750000,1000000},{10,20,35,45,60}),NA()))

After further review, TAZGUY nailed it. I had to make a minor adjustment for a percentage result. Instead of 10, 20, 35, etc. I needed to have .1, .2, .35, etc. Thank you all, each suggestion would have worked. Thanks again, Dakk

Replies
7
Views
543
Replies
0
Views
205
Replies
2
Views
471
Replies
2
Views
402
Replies
3
Views
607

1,202,983
Messages
6,052,905
Members
444,611
Latest member
ggwpnore

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back