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]

(A2+B2*2+C2+D2*4)/8.
is that giving you the correct answer , as excel does do things in a specific order
not left to right

anyway

However, I need a formula that can supplant D2 for B2 when B2=97.

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

Now, l want to change it like this: if A2=97, B2=97,C2=18 and D2=19.

Not sure what that means ?
Change what exactly

 

[Fluff]

Is this a general Excel question, or is it for Power Query?

 

[John 4348]

(A2+B2*2+C2+D2*4)/8.
is that giving you the correct answer , as excel does do things in a specific order
not left to right

anyway


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


Not sure what that means ?
Change what exactly

Is this a general Excel question, or is it for Power Query?

I teach in an elementary school. taking exam, l gave a score to my students(1-20), but if one of them is absent, give him number 97. Finally, based on ( A2+ B2*2+C2+D2*4)/8; they will pass their Exams.

 

[John 4348]

(A2+B2*2+C2+D2*4)/8.
is that giving you the correct answer , as excel does do things in a specific order
not left to right

anyway


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


Not sure what that means ?
Change what exactly

My formula is (A2+B2*2+C2+B2*4)/8
However, I need a formula that can supplant C2 for A2 & D2 for B2 when A2 & B2= 97

 

[Fluff]

If you want a regular formula then you have posted in the wrong. In future please post such questions in the Excel Questions section of the board.
I have moved it for you this time.

 

[John 4348]

If you want a regular formula then you have posted in the wrong. In future please post such questions in the Excel Questions section of the board.
I have moved it for you this time.

Thank you.

 

[etaf]

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

Sorry for being specific - does that apply if A2 and B2 = 97 ,

in your first post

However, I need a formula that can supplant D2 for B2 when B2=97.

But i read it differently in the last post


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

is that correct ?

IF not can you write out like the above what you want the formula to be

then a reasonably nested IF should be possible

 

[John 4348]

Sorry for being specific - does that apply if A2 and B2 = 97 ,

in your first post

But i read it differently in the last post


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

is that correct ?

IF not can you write out like the above what you want the formula to be

then a reasonably nested IF should be possible

On sheet 1, row A2:D2, l have numbers A2= 97, 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 C2 for A2 & D2 for B2 when A2 & B2=97.

 

[John 4348]

On sheet 1, row A2:D2, l have numbers A2= 97, 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 C2 for A2 & D2 for B2 when A2 & B2=97.

I wrote this: = IF( A2=97;C2;A2+IF(B2=97;D2;B2*2)+(C2+D2*4)/8)
Result 18
But must be 18/75