I am looking for a function which does the following:

if value A is equal to or less than value B then return 0
if Value A greater than value B but less than value C then return the
difference C-B
if Value A greater than or equal to value C then return value C

I have tried using IF statements but they are not returning the values
correctly

Please can someone advise? using XL 2K7

Regards

Mark

=IF(A1<=B1,0,IF(A1<C1,C1-B1,C1))
--
David Biddulph

"Mark Scott" wrote in message
...
I am looking for a function which does the following:

if value A is equal to or less than value B then return 0
if Value A greater than value B but less than value C then return the
difference C-B
if Value A greater than or equal to value C then return value C

I have tried using IF statements but they are not returning the values
correctly

Please can someone advise? using XL 2K7

Regards

Mark

Thanks David, making it harder than it should have been :)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=IF(A1<=B1,0,IF(A1<C1,C1-B1,C1))
--
David Biddulph

"Mark Scott" wrote in message
...
I am looking for a function which does the following:

if value A is equal to or less than value B then return 0
if Value A greater than value B but less than value C then return the
difference C-B
if Value A greater than or equal to value C then return value C

I have tried using IF statements but they are not returning the values
correctly

Please can someone advise? using XL 2K7

Regards

Mark

=IF(A1<=B1,0,IF(AND(A1B1,A1<C1),C1-B1,C1))


"Mark Scott" wrote:

I am looking for a function which does the following:

if value A is equal to or less than value B then return 0
if Value A greater than value B but less than value C then return the
difference C-B
if Value A greater than or equal to value C then return value C

I have tried using IF statements but they are not returning the values
correctly

Please can someone advise? using XL 2K7

Regards

Mark

Yes, but bear in mind that you don't need the AND(A1B1, )
as we've already trapped the cases where A1<=B1 .
--
David Biddulph

"Teethless mama" wrote in message
...
=IF(A1<=B1,0,IF(AND(A1B1,A1<C1),C1-B1,C1))

"Mark Scott" wrote:

I am looking for a function which does the following:

if value A is equal to or less than value B then return 0
if Value A greater than value B but less than value C then return the
difference C-B
if Value A greater than or equal to value C then return value C

I have tried using IF statements but they are not returning the values
correctly

Please can someone advise? using XL 2K7

Regards

Mark

Just another possibility:

=(C1-B1*(A1<C1))*(A1B1)

In article ,
"David Biddulph" <groups [at] biddulph.org.uk wrote:

Yes, but bear in mind that you don't need the AND(A1B1, )
as we've already trapped the cases where A1<=B1 .
--
David Biddulph

"Teethless mama" wrote in message
...
=IF(A1<=B1,0,IF(AND(A1B1,A1<C1),C1-B1,C1))

"Mark Scott" wrote:

I am looking for a function which does the following:

if value A is equal to or less than value B then return 0
if Value A greater than value B but less than value C then return the
difference C-B
if Value A greater than or equal to value C then return value C

I have tried using IF statements but they are not returning the values
correctly

Please can someone advise? using XL 2K7

Regards

Mark