Varied percentage based on cell value

%3CLINGO-SUB%20id%3D%22lingo-sub-3103380%22%20slang%3D%22en-US%22%3EVaried%20percentage%20based%20on%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103380%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20work%20out%20a%20varied%20percentage%20of%20a%20cell%20value%20to%20display%20as%20currency.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20if%20B1%20is%20between%20%C2%A30%20and%20%C2%A32000%20then%20I%20need%20C1%20to%20display%207%25%20of%20B1.%20If%20B1%20is%20between%20%C2%A32001%20and%20%C2%A33500%20I%20need%20C1%20to%20display%208%25%20of%20B1%2C%20if%20B1%20is%20%C2%A35000%20or%20over%20I%20need%20C1%20to%20display%2010%25%20of%20B1%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3CP%3ELeon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3103380%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103444%22%20slang%3D%22en-US%22%3ERe%3A%20Varied%20percentage%20based%20on%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1294389%22%20target%3D%22_blank%22%3E%40leon_the_car_guy%3C%2FA%3E%26nbsp%3BTry%20this%20in%20C1%20and%20format%20the%20cell%20as%20a%20percentage.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLOOKUP(B1%2C%7B0%2C2001%2C3501%7D%2C%7B0.07%2C0.08%2C0.1%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103507%22%20slang%3D%22en-US%22%3ERe%3A%20Varied%20percentage%20based%20on%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103507%22%20slang%3D%22en-US%22%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%2C%20thank%20you%20so%20much%20for%20your%20response.%3CBR%20%2F%3EI%20have%20tried%20that%20and%20got%20it%20working%20to%20show%20the%20correct%20percentage%2C%20however%20I%20need%20it%20to%20show%20the%20currency%20value.%20i.e%20if%20B1%20is%20%C2%A3500%20i%20need%20C1%20to%20show%207%25%20of%20that%20as%20a%20currency%2C%20so%20%C2%A335.%20and%20if%20B1%20is%20%C2%A32768%20I%20need%20the%208%25%20to%20be%20displayed%20as%20%C2%A3221.44%20etc.%3CBR%20%2F%3EIs%20there%20a%20way%20to%20adjust%20this%20to%20achieve%20that%3F%20I%20tried%20changing%20the%20format%20to%20currency%2C%20but%20it%20just%20displayed%20%C2%A30.%3C%2FLINGO-BODY%3E
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