# Newbie need help with a formula

#### bally912

##### New Member
Hi 1st post on here and pretty new to whole excel app.

I basically need help with a formula I'm trying to put into a work sheet.

Think I need 3 fields, 1 is a rating which will be 0-100.

2 is a currency field

and the 3rd one is where I need it to work out a percentage of the currency in field 2.

I'm ok with that if the percentage was a constant, which it isn't.

I need it to determine what percentage to use by the number inputed in field 1.

i.e

0-50 = .05
51-60 = .1
61-70 = .15
71- 80 = .2
and finally anything over 81 = .25

any help would be much appreciated

cheers matt

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### jeffreybrown

##### Well-known Member
Hi Matt and Welcome to the Board,

=LOOKUP(A1,{0,51,61,71,81},{0.05,0.1,0.15,0.2,0.25})

#### Smitty

##### Legend
Welcome to the Board!

You can use a Lookup table:

Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #0000ff">Range</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #0000ff">%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">5%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">51</TD><TD style="TEXT-ALIGN: right">10%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">61</TD><TD style="TEXT-ALIGN: right">15%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">71</TD><TD style="TEXT-ALIGN: right">20%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">81</TD><TD style="TEXT-ALIGN: right">25%</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">85</TD><TD style="TEXT-ALIGN: right">25%</TD></TR></TBODY></TABLE>
Sheet1

You'll find a link here to some working examples.

HTH,

#### bally912

##### New Member
Cheers both of you, works a treat thanks very much.

Replies
5
Views
65
Replies
6
Views
74
Replies
25
Views
958
Replies
6
Views
121
Replies
0
Views
24

1,127,705
Messages
5,626,392
Members
416,180
Latest member
Excel_user12321

### 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