SOLVED

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
Highlighted
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
Highlighted

@LeDanJohnson 

Perhaps

= 2*(A1+1)*A3
Highlighted

@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)
Highlighted

@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

Highlighted

@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

 

Highlighted
Just missing the * A2
Highlighted
Solution

@RalphB57 

Yes, thank you, missed A2

image.png

 

Highlighted

@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?

Highlighted

@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, 

 

Highlighted

@LeDanJohnson 

When the formula as before shall work

Highlighted

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

Highlighted

@Subodh_Tiwari_sktneer Thanks for all your help - this works. 

 

Highlighted

@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.