What would the formula be for the cell?

[DJ Dusty]

Your help please! I am trying to establish a formula for a cell, and
need to know the sequence of IF statements to help me achieve it.

D1 is to contain the formula.

The following is the series of outcomes:

_OUTCOME_1_
A1 = 2
B1 = 0
C1 = 0

D1 is therefore to equal 2, because neither B1 nor C1 have any numbers
greater than 0.

_OUTCOME_2_
A1 = 2
B1 = 0
C1 = 2

D1 is to equal 0, because as either B1 or C1 contain a number greater
than 0, it is to be subtracted from A1.

_OUTCOME_3_
A1 = 2
B1 = 2
C1 = 2

This is where it gets tricky - If cells A1, B1 & C1 all contain the
same number, then D1 is to equal 0. I need to be able to deduct 2 from
A1.

To put it another way, if B1 and C1 contain the SAME number, it is that
number that is deducted from A1, not B1 & C1 added together. Basically,
D1 should be 0 no matter what the number was. So, if A1 = 4, B1 = 4 &
C1 = 4, the answer should still be 0. I would want to be able to deduct
4 from A1.

There must be a nested IF statement I can use to achieve this, but am
really stuck!

I'd be grateful if you could come up with the answer!

Thanks.

 

[Peo Sjoblom]

There are some questions, can D be negative? What if A1 = 2, B1 =2 and C1 =
4?

If D can be negative but you always deduct the largest value of B1 and C1

=A1-MAX(B1,C1)

if D cannot be negative but you always deduct the largest value of B1 and C1

=MAX(A1-MAX(B1,C1),0)



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[DJ Dusty]

Thanks Peo, that's fantastic - great help! It has worked a treat.

FYI, D1 could not be negative, so I've used the second of you
formulas.

Cheers

 

[Peo Sjoblom]

Thanks for the feedback

Peo