View Full Version : Tricky IF() use

Lance-R

11-16-2007, 03:30 PM

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?

yes:

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

mikerickson

11-16-2007, 04:09 PM

=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

11-16-2007, 05:37 PM

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

11-16-2007, 05:45 PM

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

11-16-2007, 06:08 PM

Lance;

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

Lance-R

11-16-2007, 06:39 PM

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

11-17-2007, 07:49 AM

Mikerikson,

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

Grant.

Piethagorus

11-18-2007, 05:54 PM

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

Grantx

11-18-2007, 10:45 PM

?

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.