Expert Needed !!!!!

[megamanno1]

Hi All,

Please can someone help me in excel 2007,

I need to create a formula to do the following:

if the price falls between £0 - £150 then to add £30 onto the price, (e.g £100 will add £30 = £130) or if it is >£150 but <=£200 then add £50, or if is >£200 but <=£300 then add £80 or if it is >£300 but <=£500 then add £100 or for anything over £500 then To add 20%.

<table style="width: 482px; height: 210px;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="mso-width-source:userset;mso-width-alt:2998; width:62pt" span="3" width="82"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl72" style="height:15.0pt;width:62pt" height="20" width="82">Price (£)</td> <td class="xl73" style="width:62pt" width="82">Markup</td> <td class="xl74" style="width:62pt" width="82">percentage</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none" height="20">£0 - £150</td> <td class="xl68" style="border-top:none">£30.00</td> <td class="xl69" style="border-top:none">N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">£151 - £200</td> <td class="xl64" style="border-top:none">£50.00</td> <td class="xl65" style="border-top:none">N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;border-top:none" height="20">£201 - £300</td> <td class="xl68" style="border-top:none">£80.00</td> <td class="xl69" style="border-top:none">N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">£301 - £500</td> <td class="xl64" style="border-top:none">£100.00</td> <td class="xl65" style="border-top:none">N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;border-top:none" height="20">£501 +</td> <td class="xl68" style="border-top:none">N/A</td> <td class="xl71" style="border-top:none">20%</td> </tr> </tbody> </table> formula will be feed off whatever I type in cell A10. (based on price range)
the formula itself will be placed in cell B10.

Any advice will be greatly appreciated !!! if this doesn't make sense let me know and I will try explain further!! THANK YOU

 

[shg]

=a10 + choose(match(a10, {0,151,201,301,501}), 30,50,80,100,20%*a10)

 

[megamanno1]

Thanks that worked a treat.

thanks again