SOLVED

Help with Excel formula

Copper 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 * 8) *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 * 8) *3

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

 

Just missing the * A2 ;)
best response confirmed by LeDanJohnson (Copper Contributor)
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.

1 best response

Accepted Solutions
best response confirmed by LeDanJohnson (Copper Contributor)
Solution

@RalphB57 

Yes, thank you, missed A2

image.png

 

View solution in original post