If funcion on big formula

[willemeulen]

I have the following formula:

Schematic:

(A+C+E) + 0,57(B+D) - 3,14d

Excel:

=(Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-3.14*Sheet1!F3

The original formula contains the following note:

If B or D > 400 add 2d
some other formulas use ≥ sign instead of >
I know what 2d will be (2*Sheet1!F3)

Is it possible to incorporate this "IF" into the function? :nuts

Thanks

 

[Eduardo]

Hi,
=if(or(B>400,D>400),Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-2*Sheet1!F3,Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-3.14*Sheet1!F3)

 

[JoeU2004]

(A+C+E) + 0,57(B+D) - 3,14d
[....]
=(Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-3.14*Sheet1!F3

The original formula contains the following note:
If B or D > 400 add 2d

=Sheet1!M3+Sheet1!O3+Sheet1!Q3 + 0.57*(Sheet1!N3+Sheet1!P3) - 3.14*Sheet1!F3
+ 2*Sheet1!F3*or(Sheet1!N3>400,Sheet1!P3>400)


----- original message -----

 

[Glenn]

I have the following formula:

Schematic:

(A+C+E) + 0,57(B+D) - 3,14d

Excel:

=(Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-3.14*Sheet1!F3

The original formula contains the following note:

If B or D > 400 add 2d
some other formulas use ≥ sign instead of >
I know what 2d will be (2*Sheet1!F3)

Is it possible to incorporate this "IF" into the function? :nuts

Thanks

=(Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-3.14*Sheet1!F3+IF(MAX(Sheet1!N3,Sheet1!P3)>400),2*Sheet1!F3,0)

 

[Glenn]

=(Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-3.14*Sheet1!F3+IF(MAX(Sheet1!N3,Sheet1!P3)>400),2*Sheet1!F3,0)

Sorry, too many ")" in that formula...corrected below:


=(Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-
3.14*Sheet1!F3+IF(MAX(Sheet1!N3,Sheet1!P3)>400,2*Sheet1!F3,0)

 

[willemeulen]

Sorry, too many ")" in that formula...corrected below:


=(Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-
3.14*Sheet1!F3+IF(MAX(Sheet1!N3,Sheet1!P3)>400,2*Sheet1!F3,0)

When I add the last bit to my formula it does not seem the affect the
outcome at all, I mean when I increase B (sheet1!N3) or D (sheet1!P3)
from 400 to 401 nothing happens (minimal increase in outcome).

Is the MAX application correct, I'm not shore but only when B or D is
bigger than 400 2d (2*d) needs to be added to the already calculated
figure.

Does OR need to be in the function?

I will try this solution on some of the other formula's as well where
the IF is only applicable for one value.

 

[Glenn]

When I add the last bit to my formula it does not seem the affect the
outcome at all, I mean when I increase B (sheet1!N3) or D (sheet1!P3)
from 400 to 401 nothing happens (minimal increase in outcome).

Is the MAX application correct, I'm not shore but only when B or D is
bigger than 400 2d (2*d) needs to be added to the already calculated
figure.

Does OR need to be in the function?

I will try this solution on some of the other formula's as well where
the IF is only applicable for one value.

The logic of the MAX() function should work fine. It just compares the largest
of the two values to 400. The smallest of the two values doesn't matter
according to your request. I used MAX() to save a couple of keystrokes over the
alternative using OR():

=(Sheet1!M3+Sheet1!O3+Sheet1!Q3)+0.57*(Sheet1!N3+Sheet1!P3)-
3.14*Sheet1!F3+IF(OR(Sheet1!N3>400,Sheet1!P3>400),2*Sheet1!F3,0)

As for your apparent lack of results, I would need to know the contents every
cell referenced in the formula, the results you expect and the results you are
getting to figure out what the problem may be.