I have a worksheet in which a formula calculates the fee based on the item price
in an adjacent cell and a lookup table which shows "breakpoints" in the fee
structure.

This works fine with any amount (including 0) in item price, but fails when the
price cell is blank. When blank, the function MIN(price,75) gives a value of 75
rather than 0.

Is this behaviour to be expected? If so, is there an elegant way to handle
cells with a blank value, as having "0.00" all over the sheet is a bit untidy?

On Sat, 06 Dec 2008 12:28:16 +0800, rebel wrote:

I have a worksheet in which a formula calculates the fee based on the item price
in an adjacent cell and a lookup table which shows "breakpoints" in the fee
structure.

This works fine with any amount (including 0) in item price, but fails when the
price cell is blank. When blank, the function MIN(price,75) gives a value of 75
rather than 0.

Is this behaviour to be expected? If so, is there an elegant way to handle
cells with a blank value, as having "0.00" all over the sheet is a bit untidy?


Something like:

=if(ItemPrice="","",YourFormula)
--ron

Both MIN and MINA functions/formulas ignore BLANKS...

If you want to treat a blank as a zero and get MIN as zero then try
=MIN(IF(A1:A100="",0,A1:A100))

This will convert all blanks to 0 in the range A1:A100 and then pass it to MIN

"Ron Rosenfeld" wrote:

On Sat, 06 Dec 2008 12:28:16 +0800, rebel wrote:

I have a worksheet in which a formula calculates the fee based on the item price
in an adjacent cell and a lookup table which shows "breakpoints" in the fee
structure.

This works fine with any amount (including 0) in item price, but fails when the
price cell is blank. When blank, the function MIN(price,75) gives a value of 75
rather than 0.

Is this behaviour to be expected? If so, is there an elegant way to handle
cells with a blank value, as having "0.00" all over the sheet is a bit untidy?


Something like:

=if(ItemPrice="","",YourFormula)
--ron

Is this behaviour to be expected?

The difference is in what functions and type of formula you're using. Some
functions evaluate an empty cell as 0 and some functions ignore empty cells.
In the case of MIN, it ignores empty cells unless *all* of the cells
referenced are empty then it will return 0.

How you deal with it depends on the application.

If you want this to return 0 when "price" is empty:

MIN(price,75)

Try it like this

MIN(price*1,75)

--
Biff
Microsoft Excel MVP


"rebel" wrote in message
...
I have a worksheet in which a formula calculates the fee based on the item
price
in an adjacent cell and a lookup table which shows "breakpoints" in the
fee
structure.

This works fine with any amount (including 0) in item price, but fails
when the
price cell is blank. When blank, the function MIN(price,75) gives a value
of 75
rather than 0.

Is this behaviour to be expected? If so, is there an elegant way to
handle
cells with a blank value, as having "0.00" all over the sheet is a bit
untidy?

Clarification:

In the case of MIN, it ignores empty cells unless *all* of the cells
referenced are empty then it will return 0.
MIN(price,75)

Ok, you might think: well, "price" is *all* of the referenced cells so why
doesn't it evaluate as 0?

Because 75 is another argument that isn't an empty referenced cell. So, I
should have said:

....it ignores empty cells unless *all* of the referenced arguments are empty
cells then it will return 0.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Is this behaviour to be expected?

The difference is in what functions and type of formula you're using. Some
functions evaluate an empty cell as 0 and some functions ignore empty
cells. In the case of MIN, it ignores empty cells unless *all* of the
cells referenced are empty then it will return 0.

How you deal with it depends on the application.

If you want this to return 0 when "price" is empty:

MIN(price,75)

Try it like this

MIN(price*1,75)

--
Biff
Microsoft Excel MVP


"rebel" wrote in message
...
I have a worksheet in which a formula calculates the fee based on the item
price
in an adjacent cell and a lookup table which shows "breakpoints" in the
fee
structure.

This works fine with any amount (including 0) in item price, but fails
when the
price cell is blank. When blank, the function MIN(price,75) gives a
value of 75
rather than 0.

Is this behaviour to be expected? If so, is there an elegant way to
handle
cells with a blank value, as having "0.00" all over the sheet is a bit
untidy?

On Fri, 5 Dec 2008 23:02:03 -0500, "T. Valko" wrote:

Clarification:

In the case of MIN, it ignores empty cells unless *all* of the cells
referenced are empty then it will return 0.
MIN(price,75)

Ok, you might think: well, "price" is *all* of the referenced cells so why
doesn't it evaluate as 0?

Because 75 is another argument that isn't an empty referenced cell. So, I
should have said:

...it ignores empty cells unless *all* of the referenced arguments are empty
cells then it will return 0.

Thanks, Biff. The "*1" has done the trick.

Each occurrence of the formula references a single target cell (albeit three
times), not a range, so this was the easiest fix.

=ROUND(MIN(E29*1,75)*0.0525+(MIN(925,MAX(E29-75,0)))*0.0275+MAX(E29-1000,0)*0.015,2)

only needed the first term modified.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"rebel" wrote in message
...
On Fri, 5 Dec 2008 23:02:03 -0500, "T. Valko"
wrote:

Clarification:

In the case of MIN, it ignores empty cells unless *all* of the cells
referenced are empty then it will return 0.
MIN(price,75)

Ok, you might think: well, "price" is *all* of the referenced cells so why
doesn't it evaluate as 0?

Because 75 is another argument that isn't an empty referenced cell. So, I
should have said:

...it ignores empty cells unless *all* of the referenced arguments are
empty
cells then it will return 0.

Thanks, Biff. The "*1" has done the trick.

Each occurrence of the formula references a single target cell (albeit
three
times), not a range, so this was the easiest fix.

=ROUND(MIN(E29*1,75)*0.0525+(MIN(925,MAX(E29-75,0)))*0.0275+MAX(E29-1000,0)*0.015,2)

only needed the first term modified.

Ah, the irony. Straight after I posted, I see the other post "Cell is not
blank" - serendipitous.