Simple Prorate Formula off matrix

[shepcs]

Hi
Anyone know a way to do a simple prorate formula off a matrix? I can do if/then but assuming there may be an easier way.

Book1
	C	D	E	F	G
4	Rating			Actual	Prorated %
5	1	0%		1.25
6	2	50%		2.6
7	3	100%		3.4
8	4	125%		4.8
9	5	150%		5
Sheet1

 

[awoohaw]

What are you prorating? Expected results would be nice.
Does this get what you ask:

Book1
	C	D	E	F	G	H
4	Rating			Actual	Prorated %	Prorated % X Actual
5	1	0.00%		1.25	0	0
6	2	50.00%		2.6	0.5	1.3
7	3	100.00%		3.4	1	3.4
8	4	125.00%		4.8	1.25	6
9	5	150.00%		5	1.5	7.5
Sheet1

Cell Formulas
Range	Formula
G5:G9	G5	=LOOKUP(F5:F9,$C$5:$C$9,$D$5:$D$9)
H5:H9	H5	=(F5:F9)*(LOOKUP(F5:F9,$C$5:$C$9,$D$5:$D$9))
Dynamic array formulas.

 

[shepcs]

Thankis awoohaw. I probably shodul have been more specific. So if you're at "3.3" it would prorate the distance between 3 and 4, so hte answer on the associated % is 107.5%. I can do it with If/Then but I was wondering if there's a more elegant way?

Actual Rating	3.30
Percentage	107.5%
Rating
1	0%
2	50%	0%
3	100%	0%
4	125%	108%
5	150%	0%

 

[RoryA]

Can the rating ever go above 5 (or below 1)? If so, what should happen?

 

[RoryA]

If it's only ever 1-5, you could do something like this:

=LET(ratings,$C$5:$C$9,pcts,$D$5:$D$9,mr,XMATCH(F5,ratings,-1),mv,INDEX(pcts,mr),IF(INDEX(ratings,mr)=F5,mv,mv+(INDEX(pcts,mr+1)-mv)*MOD(F5,1)))

 

[awoohaw]

Here is another try at it, again, same restrictions as @RoryA

Book1
	A	B
1	Actual Rating	3.3
2	Percentage	107.50%
3	Rating
4	1	0.00%
5	2	50.00%
6	3	100.00%
7	4	125.00%
8	5	150.00%
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LET( ActRtgMod,MOD(B1,1), UpRngPct, LOOKUP(ROUNDUP($B$1,0),$A$4:$A$8,$B$4:$B$8), LoRngPct, LOOKUP($B$1,$A$4:$A$8,$B$4:$B$8), ActRtgMod*(UpRngPct-LoRngPct)+LoRngPct)

 

[shepcs]

Thank you both! Let me try them. No it couldn't go above a 1 or a 5.
Thanks again!