calculating percentage based on preset criteria

[dakk]

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.

 

[barry houdini]

You could set up a table as shown below.....

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														value	B	C
2														0	0%	10%
3														250000	0%	20%
4														500000	30%	35%
5														750000	40%	45%
6														1000000	50%	60%
7	B						777777			40%
8	C						1212121			60%
9	A						123456			#N/A
10
Sheet3

Formula in J7 copied down column

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

 

[Ron Morris]

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

 

[Tazguy37]

How about:

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()))

Adjust ranges as necessary. HTH

 

[dakk]

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