Forumla for a sliding scale

[theadman7]

I am new here so first of all I just want to say hello to everyone.

I'm trying to incorporate a slinding scale into an excel spreadsheet so a column will be automatically populated based on a value in a seperate cell.

The scale I am incorporating is as follows

<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=216><COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 107pt; HEIGHT: 16.5pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl67 height=22 width=142>Amount (£)</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl68 width=74>Fee (£) </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1.5pt solid" class=xl69 height=21>Under £300</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl65>£ 35.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£301 to £500</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 50.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£501 to £1,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 75.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£1,001 to £1,500</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 80.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£1,501 to £3,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 95.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£3,001 to £5,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 120.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£5,001 to £15,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 245.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£15,001 to £50,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 395.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£50,001 to £100,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 685.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£100,001 to £150,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 885.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£150,001 to £200,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 1,080.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£200,001 to £250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 1,275.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 height=20>£250,000 to £300,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>£ 1,475.00 </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl71 height=21>£300,001 and above</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl72>£ 1,670.00 </TD></TR></TBODY></TABLE>

But I cant for the life of me work out the correct formula for this. Any guidance will be greatly appreciated.

Thank you

 

[VoG]

Try

=LOOKUP(A1,{0,301,501,1001,1501,3001,5001,15001,50001,100001,150001,200001,250001,300001},{35,50,75,80,95,120,245,395,685,885,1080,1275,1475,1670})

 

[theadman7]

Excellent, works perfectly, thank you!!

Try

=LOOKUP(A1,{0,301,501,1001,1501,3001,5001,15001,50001,100001,150001,200001,250001,300001},{35,50,75,80,95,120,245,395,685,885,1080,1275,1475,1670})

 

[ssingh75]

Hi,

Try the below code,,

My formula=IF(H1<=300,35,IF(AND(H1>301,H1<=500),50,IF(AND(H1>501,H1<=1000),75,IF(AND(H1>1001,H1<=1500),80,IF(AND(H1>1501,H1<=3000),95,IF(AND(H1>3001,H1<=5000),120,IF(AND(H1>5001,H1<=15000),245,IF(AND(H1>15001,H1<=50000),395,IF(AND(H1>50001,H1<=100000),685,IF(AND(H1>100001,H1<=150000),885,IF(AND(H1>150001,H1<=200000),1080,IF(AND(H1>200001,H1<=250000),1275,IF(AND(H1>250001,H1<=300000),1475,IF(H1>=300001,1670,0))))))))))))))

Hope this will help


Susheel