SOLVED
Home

Help with Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-859908%22%20slang%3D%22en-US%22%3EHelp%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859908%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20pulling%20my%20hair%20out%20trying%20to%20write%20an%20Excel%20formula%20for%20the%20following%20action%20-%20can%20you%20help.%20Essentially%2C%20depending%20on%20the%20value%20entered%20into%20A1%2C%20the%20value%20in%20A2%20should%20be%20multiplied%20by%204%2C%206%2C%208%2C%20or%2010.%20Here's%20the%20description%20of%20what%20I%20need%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20A1%20%3D%201%2C%20then%20MULTIPLY%20A2%20by%204%20and%20MULTIPLY%20the%20result%20by%20A3%3CBR%20%2F%3EBUT%3CBR%20%2F%3EIf%20A1%20%3D%202%2C%20then%20MULTIPLY%20A2%20by%206%20and%20MULTIPLY%20the%20result%20by%20A3%3CBR%20%2F%3EBUT%3CBR%20%2F%3EIf%20A1%20%3D%203%2C%20then%20MULTIPLY%20A2%20by%208%20and%20MULTIPLY%20the%20result%20by%20A3%3CBR%20%2F%3EBUT%3CBR%20%2F%3EIf%20A1%20%3D%204%2C%20then%20MULTIPLY%20A2%20by%2010%20and%20MULTIPLY%20the%20result%20by%20A3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%20you%20can%20give!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-859908%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-860027%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410599%22%20target%3D%22_blank%22%3E%40LeDanJohnson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%202*(A1%2B1)*A3%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860119%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860119%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410599%22%20target%3D%22_blank%22%3E%40LeDanJohnson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%2C%20it%20should%20be...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%202*(A1%2B1)*A2*A3%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAnd%20what%20if%20A1%3D0%3F%3C%2FP%3E%3CP%3EIf%20you%20don't%20want%20to%20calculate%20when%20A2%3D0%20or%20blank%2C%20then%20it%20should%20be...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(A1%3D0%2C%22%22%2C%202*(A1%2B1)*A2*A3)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAnd%20what%20if%20A1%26gt%3B4%2C%20would%20you%20like%20to%20calculate%20by%20following%20the%20pattern%20or%20you%20would%20not%20like%20to%20calculate%20then%3F%3C%2FP%3E%3CP%3EIf%20you%20don't%20want%20to%20calculate%20if%20either%20A1%3D0%20or%20A1%26gt%3B4%20then...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(OR(A1%3D0%2CA1%26gt%3B4)%2C%22%22%2C%202*(A1%2B1)*A2*A3)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860454%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860454%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%2C%20but%20I%20don't%20think%20this%20formula%20will%20do%20what%20I%20need.%20Users%20can%20only%20enter%201%2C%202%2C%203%20or%204%20into%20A1%2C%20so%20the%20formula%20I'm%20trying%20to%20create%20needs%20to%20say%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%201%20is%20entered%20into%20A1%2C%20do%20(A2%20*%204)%20*3%3C%2FP%3E%3CP%3EIf%202%20is%20entered%20into%20A1%2C%20do%20(A2%20*%206)%20*3%3C%2FP%3E%3CP%3EIf%203%20is%20entered%20into%20A1%2C%20do%20(A2%20*%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%20*3%3C%2FP%3E%3CP%3EIf%204%20is%20entered%20into%20A1%2C%20do%20(A2%20*%2010)%20*3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860451%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThanks%2C%20but%20I%20don't%20think%20that%20quite%20covers%20what%20I%20need.%20Users%20are%20limited%20to%20the%20number%20they%20can%20enter%20into%20A1%20-%20it's%20either%201%2C%202%2C%203%20or%204.%20The%20difficulty%20I'm%20having%20is%20in%20writing%20a%20formula%20that%20says%3A%3C%2FP%3E%3CP%3EIf%201%20is%20entered%20into%20A1%2C%20do%20(A2%20*%204)%20*3%3C%2FP%3E%3CP%3EIf%202%20is%20entered%20into%20A1%2C%20do%20(A2%20*%206)%20*3%3C%2FP%3E%3CP%3EIf%203%20is%20entered%20into%20A1%2C%20do%20(A2%20*%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%20*3%3C%2FP%3E%3CP%3EIf%204%20is%20entered%20into%20A1%2C%20do%20(A2%20*%2010)%20*3%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860528%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860528%22%20slang%3D%22en-US%22%3EJust%20missing%20the%20*%20A2%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860638%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860638%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410806%22%20target%3D%22_blank%22%3E%40RalphB57%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20thank%20you%2C%20missed%20A2%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20242px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132304i7E75537442FF4BAB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860640%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860640%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410599%22%20target%3D%22_blank%22%3E%40LeDanJohnson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20variant%20of%20requirements%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EIf%20A1%20%3D%201%2C%20then%20MULTIPLY%20A2%20by%204%20and%20MULTIPLY%20the%20result%20by%20A3%0ABUT%0AIf%20A1%20%3D%202%2C%20then%20MULTIPLY%20A2%20by%206%20and%20MULTIPLY%20the%20result%20by%20A3%0ABUT%0AIf%20A1%20%3D%203%2C%20then%20MULTIPLY%20A2%20by%208%20and%20MULTIPLY%20the%20result%20by%20A3%0ABUT%0AIf%20A1%20%3D%204%2C%20then%20MULTIPLY%20A2%20by%2010%20and%20MULTIPLY%20the%20result%20by%20A3%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ELatest%20one%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EIf%201%20is%20entered%20into%20A1%2C%20do%20(A2%20*%204)%20*3%0AIf%202%20is%20entered%20into%20A1%2C%20do%20(A2%20*%206)%20*3%0AIf%203%20is%20entered%20into%20A1%2C%20do%20(A2%20*%20*3%0AIf%204%20is%20entered%20into%20A1%2C%20do%20(A2%20*%2010)%20*3%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWhich%20one%20is%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860657%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860657%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BIn%20the%20second%20version%2C%20I%20should%20have%20written%20*A3%20(not%20*3).%20With%20that%20amend%2C%20the%20two%20requirements%20are%20the%20same%20-%20I%20tried%20to%20simplify%20them%20in%20the%20second%20version.%20Thanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860661%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860661%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410599%22%20target%3D%22_blank%22%3E%40LeDanJohnson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20the%20formula%20as%20before%20shall%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860776%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20see%20how%20it%20works%20now.%20Thanks%20for%20taking%20the%20time%20to%20solve%20this%20for%20me.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860779%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860779%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThanks%20for%20all%20your%20help%20-%20this%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860796%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410599%22%20target%3D%22_blank%22%3E%40LeDanJohnson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20we%20could%20help.%3C%2FP%3E%3CP%3ENow%20you%20may%20mark%20your%20question%20as%20Solved%20by%20choosing%20one%20of%20the%20posts%20which%20resolved%20your%20question%20as%20Best%20Answer%2FResponse.%3C%2FP%3E%3CP%3EYou%20may%20also%20click%20on%20Like%20buttons%20under%20the%20posts%20which%20you%20found%20helpful%2C%20that's%20another%20way%20to%20say%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
LeDanJohnson
Occasional Contributor

Hi, I am pulling my hair out trying to write an Excel formula for the following action - can you help. Essentially, depending on the value entered into A1, the value in A2 should be multiplied by 4, 6, 8, or 10. Here's the description of what I need:

 

If A1 = 1, then MULTIPLY A2 by 4 and MULTIPLY the result by A3
BUT
If A1 = 2, then MULTIPLY A2 by 6 and MULTIPLY the result by A3
BUT
If A1 = 3, then MULTIPLY A2 by 8 and MULTIPLY the result by A3
BUT
If A1 = 4, then MULTIPLY A2 by 10 and MULTIPLY the result by A3

 

Thanks for any help you can give!

 

12 Replies

@LeDanJohnson 

Perhaps

= 2*(A1+1)*A3

@LeDanJohnson 

I guess, it should be...

= 2*(A1+1)*A2*A3

And what if A1=0?

If you don't want to calculate when A2=0 or blank, then it should be...

=IF(A1=0,"", 2*(A1+1)*A2*A3)

And what if A1>4, would you like to calculate by following the pattern or you would not like to calculate then?

If you don't want to calculate if either A1=0 or A1>4 then...

=IF(OR(A1=0,A1>4),"", 2*(A1+1)*A2*A3)

@Subodh_Tiwari_sktneer Thanks, but I don't think that quite covers what I need. Users are limited to the number they can enter into A1 - it's either 1, 2, 3 or 4. The difficulty I'm having is in writing a formula that says:

If 1 is entered into A1, do (A2 * 4) *3

If 2 is entered into A1, do (A2 * 6) *3

If 3 is entered into A1, do (A2 * *3

If 4 is entered into A1, do (A2 * 10) *3

@Sergei Baklan Thanks, but I don't think this formula will do what I need. Users can only enter 1, 2, 3 or 4 into A1, so the formula I'm trying to create needs to say:

 

If 1 is entered into A1, do (A2 * 4) *3

If 2 is entered into A1, do (A2 * 6) *3

If 3 is entered into A1, do (A2 * *3

If 4 is entered into A1, do (A2 * 10) *3

 

Just missing the * A2
Solution

@RalphB57 

Yes, thank you, missed A2

image.png

 

@LeDanJohnson 

First variant of requirements is

If A1 = 1, then MULTIPLY A2 by 4 and MULTIPLY the result by A3
BUT
If A1 = 2, then MULTIPLY A2 by 6 and MULTIPLY the result by A3
BUT
If A1 = 3, then MULTIPLY A2 by 8 and MULTIPLY the result by A3
BUT
If A1 = 4, then MULTIPLY A2 by 10 and MULTIPLY the result by A3

Latest one

If 1 is entered into A1, do (A2 * 4) *3
If 2 is entered into A1, do (A2 * 6) *3
If 3 is entered into A1, do (A2 * *3
If 4 is entered into A1, do (A2 * 10) *3

Which one is correct?

@Sergei Baklan In the second version, I should have written *A3 (not *3). With that amend, the two requirements are the same - I tried to simplify them in the second version. Thanks, 

 

@LeDanJohnson 

When the formula as before shall work

@Sergei Baklan I see how it works now. Thanks for taking the time to solve this for me. 

@Subodh_Tiwari_sktneer Thanks for all your help - this works. 

 

@LeDanJohnson 

You're welcome! Glad we could help.

Now you may mark your question as Solved by choosing one of the posts which resolved your question as Best Answer/Response.

You may also click on Like buttons under the posts which you found helpful, that's another way to say thanks.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 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
13 Replies