Excell formula help

B

Brian

I have this formula in a cell (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95+BJ94).
this formula does not function correctly in all conditions. In certain
conditions it needs to run like this
(=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95). and in another condition it need
to be this (=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113)

What I need to do is set a condition something like this =if(Bf113<bf109)
choose a say this formula
(=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(bf109-bf113)

So the final question I have is! Is it possible to set say three conditions
and have a formula and it choose a separate formula per each condition. in a
single cell.
 
S

Sandy Mann

Try:

=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-(BF109-BF113)*(BF113<BF109)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

Or with fewer key stroks but with one more function call:

=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95)-MAX(BF109-BF113,0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

That will of course subtract from either BJ95 or BJ95+BJ96 when ever
BF113<BF109 is true which may not be what you want.

Actually now that I take the time to read your formula:

SUM(BJ94:BJ96) must always be equal to or bigger than BJ95 because BJ95 is
contained withing the SUM() so the test must always be False!

Did you mean SUM(BJ94,BJ96) which is BJ94 + BJ96?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

No I'm wrong again! BJ94 or BJ96 could be negative.

So assuming that the test is correctly written to subtract from BJ96+BJ95
only use:

=IF(SUM(BJ94:BJ96)<BJ95,BJ95,BJ96+BJ95-MAX(BF109-BF113,0))

To subtract from BJ95 only use:

=IF(SUM(BJ94:BJ96)<BJ95-MAX(BF109-BF113,0),BJ95,BJ96+BJ95)

To subtract from either use my previous formula.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
B

Brian

Sandy, Hi!
what I'm trying to do is set three conditions each with formula set to each
condition. I'm thinking I don't need theis part of the existing formula
(=IF(SUM(BJ94:BJ96)<BJ95,BJ95 and I can get rid of an if function

The following are two of the three formulas that work independently.

Formula if Condition BF109 >BF113 exists!
=IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95)

Formula if condition BF109<BF113 exists!
=IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95+BA94)

I'm so confussed right now I can't remember the third condition?

Thanks for your help!
 
S

Sandy Mann

I'm so confussed right now I can't remember the third condition?

You're confused? <g>

But you still don't say when you want to subtract (BF109-BF113).

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

mmmmm.....

But you still don't say when you want to subtract (BF109-BF113).
Click to expand...

Not a ver informative question is it?

What I meant was if (BF109<BF113) do you want to subtract (BF109-BF113) from
BA95 or BA96+BA95+BA94?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

Brian,

Re-reading the posts is seems to me that you may want (BF109-BF113)
subtracted from the minimum of BJ95 or SUM(BJ94:BJ96) but only if
BF109>BF113.

If that is the case then:

=MIN(SUM(BJ94:BJ96),BJ95)-MAX(BF109-BF113,0)

Should do it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Rick Rothstein \(MVP - VB\)

what I'm trying to do is set three conditions each with formula set to
each
condition. I'm thinking I don't need theis part of the existing formula
(=IF(SUM(BJ94:BJ96)<BJ95,BJ95 and I can get rid of an if function

The following are two of the three formulas that work independently.

Formula if Condition BF109 >BF113 exists!
=IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95)

Formula if condition BF109<BF113 exists!
=IF(SUM(BA94:BA96)<BA95,BA95,BA96+BA95+BA94)
Click to expand...

I know I am jumping into this thread kind of late, but I am confused by your
first test in the above two IF statements.

SUM(BA94:BA96)<BA95

Unless the sum of the contents of BA94 and BA96 are negative, your test will
**always** be true. Why? Because you are including BA95 in the summation
range. You test condition expands to this statement...

BA94 + BA95 + BA96 < BA95

which is equivalent, when reduced, to this test expression...

BA94 + BA96 < 0

Is this really what you are meaning to test?

As for a three condition test, consider this...

=IF(Condition1,TrueForCondition1,IF(Condition2,TrueForCondition2,IF(Condition3,TrueForCondition3,FalseForAllConditions)))

Rick
 
Top