i don't want negative values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
My spreadsheet displays freight costs to the US for our company. Sheet 1 has the rates for core carriers and Sheet 2 has the rates for spot carriers. Spot carriers are paid $50 less than core carriers. Sheet 1 has the value and I'm trying to make a formula for the cells in Sheet 2 so that they only display POSITIVE VALUES.
For example, a cell on Sheet 1 has a value of $1650, the formula for the corresponding cell is =Sheet1!C3-50 and it correctly displays $1600 on Sheet 2. HOWEVER, where there are no rates on Sheet 1, the formula displays -$50 on Sheet 2 where it has been applied. I need the formula to apply to ALL cells in the field whether or not they have values on Sheet 1 or not at this time
Is there a way for me to have a blank cell displayed in the place of a negative value?
 
Hi

You could add a MAX function into your formulae:
=MAX(Sheet1!C3-50,0)
This will pick the biggest of these numbers, so if Sheet1!C3-50 is negative,
it will display 0
You can hide zeroes either with a custom number format or by using Tools /
Options / View and uncheck Zero Values.

--
Andy.


Ken said:
Hi.
My spreadsheet displays freight costs to the US for our company. Sheet 1
Click to expand...
has the rates for core carriers and Sheet 2 has the rates for spot carriers.
Spot carriers are paid $50 less than core carriers. Sheet 1 has the value
and I'm trying to make a formula for the cells in Sheet 2 so that they only
display POSITIVE VALUES.
For example, a cell on Sheet 1 has a value of $1650, the formula for the
Click to expand...
corresponding cell is =Sheet1!C3-50 and it correctly displays $1600 on Sheet
2. HOWEVER, where there are no rates on Sheet 1, the formula displays -$50
on Sheet 2 where it has been applied. I need the formula to apply to ALL
cells in the field whether or not they have values on Sheet 1 or not at this
time.
Is there a way for me to have a blank cell displayed in the place of a
Click to expand...
negative value?
 
You can have a blank cell "displayed" by going into conditional formatting
on your cell(s), say A1, select formula and enter
=A1<0

then select the font color format to be the same as the background (white
for example)

You can have a blank cell (rather, er, to be precise, a 0-length text cell)
returned by wrapping something like

=IF(your_formula<0,"",your_formula)
around your_formula.



Ken said:
Hi.
My spreadsheet displays freight costs to the US for our company. Sheet 1
Click to expand...
has the rates for core carriers and Sheet 2 has the rates for spot carriers.
Spot carriers are paid $50 less than core carriers. Sheet 1 has the value
and I'm trying to make a formula for the cells in Sheet 2 so that they only
display POSITIVE VALUES.
For example, a cell on Sheet 1 has a value of $1650, the formula for the
Click to expand...
corresponding cell is =Sheet1!C3-50 and it correctly displays $1600 on Sheet
2. HOWEVER, where there are no rates on Sheet 1, the formula displays -$50
on Sheet 2 where it has been applied. I need the formula to apply to ALL
cells in the field whether or not they have values on Sheet 1 or not at this
time.
Is there a way for me to have a blank cell displayed in the place of a
Click to expand...
negative value?
 
Back
Top