Calculating Numbers

[John 4348]

Hello everyone.
In last Post,I wrote:
On sheet 1, row A2:D2, l have numbers A2= 17, B2=97, C2= 18, D2=19, and in E2 I've written the Formula: (A2+B2*2+C2+D2*4)/8.
However, I need a formula that can supplant D2 for B2 when B2=97.

Now, l want to change it like this: if A2=97, B2=97,C2=18 and D2=19.
What's the Formula?

 

[etaf]

I need a formula that can supplant C2 for A2 & D2 for B2 when A2 & B2=97.

So
The way you have written the above - then all you say when BOTH A2 AND B2 = 97 , But your formula is Either A2 OR B2

if B2 =97 - Change D2 in the formula to B2 = (A2+B2*2+C2+D2*4)/8. becomes (A2+B2*2+C2+B2*4)/8
if A2 =97 - Change C2 in the formula to A2 = (A2+B2*2+C2+D2*4)/8. becomes (A2+B2*2+A2+D2*4)/8
if both A2 =97 and B2 = 97 then = (A2+B2*2+C2+D2*4)/8. becomes (A2+B2*2+A2+B2*4)/8

IF( AND(A2=97, B2=97) , (A2+B2*2+A2+B2*4)/8, IF( A2=97 , (A2+B2*2+A2+D2*4)/8 , If( B2=97 , (A2+B2*2+C2+B2*4)/8 , A2+B2*2+C2+D2*4)/8 )))

Just using (A2+B2*2+C2+D2*4)/8. with A2= 97, B2=97, C2= 18, D2=19, the result is 48.125
NOTE as i said in the 1st post excel calculates a specific way - NOT left to right BUT certain things first in order
(Parentheses, Exponents, Multiplication, Division, Addition, Subtraction)
SO
(A2+B2*2+C2+D2*4)/8
NOT left to right
is NOT A2+B2 = 97+97 = 194
X 2 = 388 etc

BUT 97*2 first = 194
and then 19*4 = 76
Now add those up as in brackets
97+194+18+76 = 385
now divide by 8
48.125

IF we change D2 to B2 AND C2 to A2 we have

(A2+B2*2+A2+B2*4)/8
So first we do the mulitplcation
B2*2 = 97*2
B2*4 = 97*4
then ADD A2 and A2 = 97
776
divide by 8
=97

which i have in E2

Book4
	A	B	C	D	E
1
2	97	97	18	19	97
3
Sheet2

Cell Formulas
Range	Formula
E2	E2	=IF(AND(A2=97,B2=97),(A2+B2*2+A2+B2*4)/8,IF(A2=97,(A2+B2*2+A2+D2*4)/8,IF(B2=97,(A2+B2*2+C2+B2*4)/8,A2+B2*2+C2+D2*4)/8))

 

[etaf]

Book4
	A	B	C	D	E
1	supplant C2 for A2 & D2 for B2 when A2 & B2=97.
2	97	97	18	19	97
3	17	97	18	19	9.64
4
5	supplant A2 for C2 & B2 for D2 when A2 & B2=97.
6	97	97	18	19	18.75
7	17	97	18	19	2.33
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=IF(AND(A2=97,B2=97),(A2+B2*2+A2+B2*4)/8,IF(A2=97,(A2+B2*2+A2+D2*4)/8,IF(B2=97,(A2+B2*2+C2+B2*4)/8,A2+B2*2+C2+D2*4)/8))
E6:E7	E6	=IF(AND(A6=97,B6=97),(C6+D6*2+C6+D6*4)/8,IF(A6=97,(C6+B6*2+C6+D6*4)/8,IF(B6=97,(A6+D6*2+C6+D6*4)/8,A6+B6*2+C6+D6*4)/8))

 

[John 4348]

Book4
	A	B	C	D	E
1	supplant C2 for A2 & D2 for B2 when A2 & B2=97.
2	97	97	18	19	97
3	17	97	18	19	9.64
4
5	supplant A2 for C2 & B2 for D2 when A2 & B2=97.
6	97	97	18	19	18.75
7	17	97	18	19	2.33
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=IF(AND(A2=97,B2=97),(A2+B2*2+A2+B2*4)/8,IF(A2=97,(A2+B2*2+A2+D2*4)/8,IF(B2=97,(A2+B2*2+C2+B2*4)/8,A2+B2*2+C2+D2*4)/8))
E6:E7	E6	=IF(AND(A6=97,B6=97),(C6+D6*2+C6+D6*4)/8,IF(A6=97,(C6+B6*2+C6+D6*4)/8,IF(B6=97,(A6+D6*2+C6+D6*4)/8,A6+B6*2+C6+D6*4)/8))

Thank you.

 

[etaf]

you are welcome
I assume that has now solved the problem for you

IF not , let us know

 

[smozgur]

I assume that has now solved the problem for you

Me too. Therefore, I switched the marked solution post as it is supposed to be.

@John 4348 - in the future, please mark the post as the solution that answered your question instead of your feedback post as it will help future readers much better that way.

 

[John 4348]

Me too. Therefore, I switched the marked solution post as it is supposed to be.

@John 4348 - in the future, please mark the post as the solution that answered your question instead of your feedback post as it will help future readers much better that way.

Alright. Thank you.

 

[John 4348]

you are welcome
I assume that has now solved the problem for you

IF not , let us know

It has solved the problem. Thank you.

 

[etaf]

you are welcome