Hi Again

I need a formula that does the following

A1 = 41
B2 = 39
C2 = 6

=IF(B2<A1),C2 BUT DO NOT EXCEED A1

Therefore this should return 2

Any ideas

Thank you

By the sound of it, you didn't mean DO NOT EXCEED A1, but you presumably
mean DO NOT EXCEED A1-B2 ?
You could use =IF(B2<A1,MIN(C2,A1-B2),"whatever you want if B2=A1")

If the answer you want if B2=A1 is zero, you might try =MEDIAN(0,B2-A1,C2)
but in that case you might want a further trap to deal with cases where C2
is less than zero.
--
David Biddulph


"Mark D" wrote in message
...
Hi Again

I need a formula that does the following

A1 = 41
B2 = 39
C2 = 6

=IF(B2<A1),C2 BUT DO NOT EXCEED A1

Therefore this should return 2

Any ideas

Thank you

Think over your example!
Therefore this should return 2 What is this 2, it doesn't show up in the example.

=IF(B2<A1,min(C2,A1),"whatif B2=A1")

returns C2 if B2<A1 but A1 if C2A1 (won't exceed A1) but you didn't specify
the case B2=A1.

--
Regards!
Stefi



€žMark D€� ezt Ã*rta:

Hi Again

I need a formula that does the following

A1 = 41
B2 = 39
C2 = 6

=IF(B2<A1),C2 BUT DO NOT EXCEED A1

Therefore this should return 2

Any ideas

Thank you

Sorry yep that was silly

If B2 is than A1 then the result should be 0

"Stefi" wrote:

Think over your example!
Therefore this should return 2 What is this 2, it doesn't show up in the example.

=IF(B2<A1,min(C2,A1),"whatif B2=A1")

returns C2 if B2<A1 but A1 if C2A1 (won't exceed A1) but you didn't specify
the case B2=A1.

--
Regards!
Stefi



€žMark D€� ezt Ã*rta:

Hi Again

I need a formula that does the following

A1 = 41
B2 = 39
C2 = 6

=IF(B2<A1),C2 BUT DO NOT EXCEED A1

Therefore this should return 2

Any ideas

Thank you

Then
=IF(B2<A1,min(C2,A1),0)

--
Regards!
Stefi



€žMark D€� ezt Ã*rta:

Sorry yep that was silly

If B2 is than A1 then the result should be 0

"Stefi" wrote:

Think over your example!
Therefore this should return 2 What is this 2, it doesn't show up in the example.

=IF(B2<A1,min(C2,A1),"whatif B2=A1")

returns C2 if B2<A1 but A1 if C2A1 (won't exceed A1) but you didn't specify
the case B2=A1.

--
Regards!
Stefi



€žMark D€� ezt Ã*rta:

Hi Again

I need a formula that does the following

A1 = 41
B2 = 39
C2 = 6

=IF(B2<A1),C2 BUT DO NOT EXCEED A1

Therefore this should return 2

Any ideas

Thank you

"Mark D" wrote:
A1 = 41
B2 = 39
C2 = 6

=IF(B2<A1),C2 BUT DO NOT EXCEED A1
Therefore this should return 2

You are not being clear or consistent in your requirements. Neither C2 nor
A1 is 2. Do you mean (wild-ass guess):

=max(0, min(A1-B2,C2))


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

"Mark D" wrote:
Hi Again

I need a formula that does the following

A1 = 41
B2 = 39
C2 = 6

=IF(B2<A1),C2 BUT DO NOT EXCEED A1

Therefore this should return 2

Any ideas

Thank you