Why doesn't this formula work, please?

[Ed]

=IF(D26-F26>5,MIN(D26-F26,30),"0"). I also tried =IF(SUM(D26-F26)

5,MIN(D26-F26,30),"0"). But I keep getting "0", unless I enter a

number higher than "10", which is confusing, as the quantative value
here is 5.

 

[RagDyer]

Your formula states *GREATER* then 5!

Maybe you actually want this:

=IF(D26-F26>=5,MIN(D26-F26,30),0)

 

[Mike H]

Ed,

The formula should be
=IF(D26-F26>5,MIN(D26-F26,30),0)
i.e no "" around the zero but that's an aside.

The quantitative value is 5 so if D26-F26 is greater than 5 you get the
Min(result of the subtraction,30), otherwise you get zero. In short the
formula is doing exactly what your telling it to do and if that isn't what
you want post back with the actual values in D26 & f26 and the answer you
expect.

Mike

 

[Ed]

Ed,

The formula should be
=IF(D26-F26>5,MIN(D26-F26,30),0)
i.e no "" around the zero but that's an aside.

The quantitative value is 5 so if D26-F26 is greater than 5 you get the
Min(result of the subtraction,30), otherwise you get zero. In short the
formula is doing exactly what your telling it to do and if that isn't what
you want post back with the actual values in D26 & f26 and the answer you
expect.

Mike





- Show quoted text -

Mike, still no luck. This is a simple form I came up with for a
prescription reimbursement benefit we've got at our company. You can
send in amounts more than $5 (the copay) and up to $30. Column D is
the actual amount, column F is the $5 copay, so the idea is if cell D
minus cell F is greater than 5, SUM up to a maximum of $30. This
formula worked when the copay was $10, but now that I've changed it to
$5 and added the maximum of $30, it doesn't work except for amounts in
column D of $10 or more. Anything lower doesn't regiser, I get a sum
of 0. I've tried clearing the contents of the SUM column to make sure
there isn't some residual ghost something or other, but even your
formula doesn't work.

 

[Mike H]

Ed,

I knew my formula wouldn't work, a was simply pointing out that you should
be using 0 and not "0".

What you describe is what I expect to happen so lets approach this a
different way

D26=10
F26=5

what result do you expect?

can f26 contain other values? If so provide examples

D26=n1
F26=n2

expected result=?

Mike