Newbie need help with a formula

bally912

New Member
Joined
Apr 3, 2011
Messages
2
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
 

Some videos you may like

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
Joined
Jul 28, 2004
Messages
5,145
Hi Matt and Welcome to the Board,

How about...

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

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #bbb 1px solid; TEXT-ALIGN: center; 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 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>B8</TH><TD style="TEXT-ALIGN: left">=LOOKUP(A8,A2:B6)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>



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

HTH,
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top