Tricky IF() use

[Lance-R]

I'd like to have an cell formula that works like this:

=IF((X=A+B+C)>0,X,A-B-C)

where the value (X) evaluated is the value returned in a true condition, without having to repeat the formula (and cause extra typing and calculation time).

Is there a way to do this in Excel?

[Bob Phillips]

yes:


Put the X formula in a cell and refer to that cell in your main formula.

[mikerickson]

=IF((X=A+B+C)>0,X,A-B-C) is equivilant to
=IF((X=A+B+C), X, A-B-C), is equivilant to
=IF((X=A+B+C),A+B+C,A-B-C) = IF((X=A+B+C),A+(B+C),A-(B+C))

= A +( IF((X=A+B+C),1,-1) * (B+C) )

= A +( (2*(X=A+B+C)-1) * (B+C) )

if we knew what X is , it could be reduced further.

[Grantx]

Lance,

I may have completely misunderstood the question, and totally out of my depth here.

Is this a tautology or contradiction?

Even if X=A+B+C<0, the answer will always be X.

For example,

X=2+4+6, ie X=12, which is >0, therefore X.

Compare:

X=-12+-8+15, ie X=-5, which is <0, therefore not X ie -12+-8+15 =–5, which is X.

Therefore,

X=X and not X (are we venturing into aspects of quantum physics?)

Grant.

[mikerickson]

X=-200 A=1, B=1,C=1

(X=A+B+C) is false (evaluates to 0), so (X=A+B+C)>0 is False, so the return value is A-B-C, -1.

(Unless I am misinterpreting the OP)

[stanl]

Lance;

I noticed your workplace. Is the question related to Peak Shed? Stan

[Lance-R]

I didn't phrase the VBA well, since I didn't know the answer, it's not supposed to be a tautology, though of course that's what it looks like. What I was trying to ask (that was answered in a viable way by the first respondent), was if I could assign the value of the formula used in the conditional statement, so that I could then use that assignment as the 'true' response, so I didn't have to recalculate it.

Stan - It's actually related to flow and scheduling.

[Grantx]

Mikerikson,

I think the see point, now. Will have another look.

Grant.

[Piethagorus]

Ha ! and that other guy said you were probably numerate. :whistle:

[Grantx]

?