difference between blank and zero ....

[Ron Rosenfeld]

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

 

[T. Valko]

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)

 

[Sheeloo]

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

 

[T. Valko]

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.

 

[rebel]

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?

 

[rebel]

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

 

[rebel]

Clarification:


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.

 

[T. Valko]

You're welcome. Thanks for the feedback!