SOLVED

If function with quantities (range) [in french fonction SI]

%3CLINGO-SUB%20id%3D%22lingo-sub-3177675%22%20slang%3D%22en-US%22%3EIf%20function%20with%20quantities%20(range)%20%5Bin%20french%20fonction%20SI%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3177675%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20Calculate%20prices%20depending%20on%20quantities%20with%20variable%20prices%20depending%20on%20quantity%20input.%3C%2FP%3E%3CP%3ECell%20C218%20is%20the%20quanty%20that%20the%20user%20needs%20to%20fill%3C%2FP%3E%3CP%3ECell%20G218%20is%20the%20price%20that%20needs%20to%20be%20calculated%20(the%20If%20function)%20depending%20on%20what%20the%20user%20inputs%20on%20C218%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20C218%20is%20empty%20then%20G218%20should%20show%200%3C%2FP%3E%3CP%3EIf%20C218%20is%20between%201-4%20then%20G218%20should%20equal%20C218%20*%20S217%20%5BS217%20contains%20a%20figure%20ie%20%241000)%3C%2FP%3E%3CP%3Eif%20C218%20is%20between%205-9%20then%20G218%20should%20equal%20C218%20*%20S218%20%5BS218%20would%20be%20%24800%20for%20instance)%3C%2FP%3E%3CP%3Eif%20C218%20is%20between%2010-15%20then%20G218%20should%20equal%20C218%20*%20S219%20%5BS219%20would%20be%20%24700%20for%20instance)%3C%2FP%3E%3CP%3Eif%20C218%20is%2050%20or%20more%20then%20G218%20should%20equal%20C218%20*%20S220%20%5BS220%20%3D%20%24500%20for%20instance)%3C%2FP%3E%3CP%3EYou%20get%20the%20idea%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3177675%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-3177756%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20with%20quantities%20(range)%20%5Bin%20french%20fonction%20SI%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3177756%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1309945%22%20target%3D%22_blank%22%3E%40G-Raph%3C%2FA%3E%26nbsp%3BI%20would%20use%20a%20small%20lookup%20table%20as%20demonstrated%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3177769%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20with%20quantities%20(range)%20%5Bin%20french%20fonction%20SI%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3177769%22%20slang%3D%22en-US%22%3EThanks.%20That%20looks%20much%20easier.%3CBR%20%2F%3EQuick%20question%2C%20how%20do%20you%20setup%20the%20lower%20%26amp%3B%20price%20ranges%2Ftables%20%3F%20(noob%20question%20I%20know)%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I need to Calculate prices depending on quantities with variable prices depending on quantity input.

Cell C218 is the quanty that the user needs to fill

Cell G218 is the price that needs to be calculated (the If function) depending on what the user inputs on C218

 

if C218 is empty then G218 should show 0

If C218 is between 1-4 then G218 should equal C218 * S217 [S217 contains a figure ie $1000)

if C218 is between 5-9 then G218 should equal C218 * S218 [S218 would be $800 for instance)

if C218 is between 10-15 then G218 should equal C218 * S219 [S219 would be $700 for instance)

if C218 is 50 or more then G218 should equal C218 * S220 [S220 = $500 for instance)

You get the idea

 

Thank

4 Replies
best response confirmed by G-Raph (New Contributor)
Solution

@G-Raph I would use a small lookup table as demonstrated in the attached file.

Thanks. That looks much easier.
Quick question, how do you setup the lower & price ranges/tables ? (noob question I know)

@G-Raph Just type them anywhere. doesn't have to be on the same sheet.  The link below takes you to the MS help page on Named Ranges. I can't really explain it any better myself.

https://support.microsoft.com/en-us/office/create-a-named-range-from-selected-cells-in-a-worksheet-f... 

Perfect, that was easy. Thanks so much.