Varied percentage based on cell value

New Contributor

Hello

 

I'm trying to work out a varied percentage of a cell value to display as currency. 

For example if B1 is between £0 and £2000 then I need C1 to display 7% of B1. If B1 is between £2001 and £3500 I need C1 to display 8% of B1, if B1 is £5000 or over I need C1 to display 10% of B1 etc. 

 

Any help would be greatly appreciated. 

 

Kind regards

Leon

4 Replies

@leon_the_car_guy Try this in C1 and format the cell as a percentage.

=LOOKUP(B1,{0,2001,3501},{0.07,0.08,0.1})

 

Hello @Riny_van_Eekelen, thank you so much for your response.
I have tried that and got it working to show the correct percentage, however I need it to show the currency value. i.e if B1 is £500 i need C1 to show 7% of that as a currency, so £35. and if B1 is £2768 I need the 8% to be displayed as £221.44 etc.
Is there a way to adjust this to achieve that? I tried changing the format to currency, but it just displayed £0.

@leon_the_car_guy 

I guess that will be =B1*LOOKUP(...)

Hey thank you so much for every ones help, It's doing exactly what I want now. I know that might have been a bit of a basic question, but I'm only just getting started using formulas. I really appreciate your help.
Kind regards
Leon