SOLVED
Home

Help with a Formula - Might be IF

%3CLINGO-SUB%20id%3D%22lingo-sub-650772%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20Formula%20-%20Might%20be%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650772%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20formula%20to%20do%20the%20following.%20I%20believe%20it%20is%20an%20%22IF%22%2C%20but%20not%20sure.%20I%20basically%20want%20a%20formula%20to%20return%20results%20based%20on%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20cell%20with%20the%20number%20is%20%26lt%3B%3D50%2C%20then%20multiply%20by%202%3C%2FP%3E%3CP%3EIf%20the%20cell%20with%20the%20number%20is%20%26gt%3B50%2C%20but%20%26lt%3B%3D100%2C%20then%20multiply%20by%201.75%3C%2FP%3E%3CP%3EIf%20the%20cell%20with%20the%20number%20is%20%26gt%3B100%2C%20but%20%26lt%3B%3D200%2C%20then%20multiply%20by%201.50%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20combine%20in%20one%20formula%20so%20that%20I%20can%20copy%20it%20all%20the%20way%20down%20the%20spreadsheet.%20Any%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-650772%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-650801%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20Formula%20-%20Might%20be%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650801%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20IF%20along%20with%20AND%20to%20do%20this%20as%20follows.%3C%2FP%3E%3CPRE%3E%3DIF(A1%26lt%3B%3D50%2CA1*2%2C%3CBR%20%2F%3EIF(AND(A1%26gt%3B50%2CA1%26lt%3B%3D100)%2CA1*1.75%2C%3CBR%20%2F%3EIF(AND(A1%26gt%3B100%2CA1%26lt%3B%3D200)%2CA1*1.5%2C%22N%2FA%22)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349610%22%20target%3D%22_blank%22%3E%40bengal1022%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-650874%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20Formula%20-%20Might%20be%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20not%20challenging%20the%20correctness%20of%20your%20solution%2C%20merely%20that%20it%20can%20be%20simplified%20by%20taking%20into%20account%20that%20the%20inner%20tests%20needn't%20confirm%20that%20the%20outer%20test%20has%20given%20false%3B%20if%20it%20hadn't%2C%20the%20inner%20test%20would%20not%20be%20executed.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(%20A1%26lt%3B%3D50%2C%20A1*2%2C%20%3C%2FSTRONG%3E%3CSTRONG%3EIF(A1%26lt%3B%3D100%2C%20A1*1.75%2C%20%3C%2FSTRONG%3E%3CSTRONG%3EIF(A1%26lt%3B%3D200%2C%20A1*1.5%2C%22N%2FA%22)))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAnother%20possibility%20if%20an%20up%20to%20date%20version%20of%20Excel%20is%20being%20used%20is%20the%20IFS%20function%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20IFS(%20A1%26lt%3B%3D50%2C%202%2C%20A1%26lt%3B%3D100%2C%201.75%2C%20A1%26lt%3B%3D200%2C%201.5%2C%201%2C%20NA()%20)%20*%20A1%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-651281%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20Formula%20-%20Might%20be%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-651281%22%20slang%3D%22en-US%22%3EThis%20worked.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20You!%3C%2FLINGO-BODY%3E
bengal1022
New Contributor

 

 

I am looking for a formula to do the following. I believe it is an "IF", but not sure. I basically want a formula to return results based on the following:

 

If the cell with the number is <=50, then multiply by 2

If the cell with the number is >50, but <=100, then multiply by 1.75

If the cell with the number is >100, but <=200, then multiply by 1.50

 

I am trying to combine in one formula so that I can copy it all the way down the spreadsheet. Any help would be greatly appreciated.

 

 

3 Replies
Solution

Hi,

 

You need to IF along with AND to do this as follows.

=IF(A1<=50,A1*2,
IF(AND(A1>50,A1<=100),A1*1.75,
IF(AND(A1>100,A1<=200),A1*1.5,"N/A")))

 

Hope that helps

 

@bengal1022 

@Haytham Amairah 

This is not challenging the correctness of your solution, merely that it can be simplified by taking into account that the inner tests needn't confirm that the outer test has given false; if it hadn't, the inner test would not be executed.

=IF( A1<=50, A1*2, IF(A1<=100, A1*1.75, IF(A1<=200, A1*1.5,"N/A")))

Another possibility if an up to date version of Excel is being used is the IFS function

= IFS( A1<=50, 2, A1<=100, 1.75, A1<=200, 1.5, 1, NA() ) * A1

This worked.

Thank You!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies