New Contributor

# Varied percentage based on cell value

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

# Re: Varied percentage based on cell value

@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})``

# Re: Varied percentage based on cell value

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.

# Re: Varied percentage based on cell value

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

# Re: Varied percentage based on cell value

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