Negative

[Jon]

I have a summary sheet for a month's worth of data and I need the negative's
that appear to come out as zero's

 

[Pete_UK]

Try this amendment to your existing formulae in those cells:

=MAX(0,existing_formula)

Hope this helps.

Pete

 

[Jacob Skaria]

Custom format the cells to the below (from menu Format>Cell>Custom>Type)

General;"0";0

 

[BSc Chem Eng Rick]

Hi Jon,

Use this formula:
=IF("Cell Reference"<0,0,"Cell Reference")

 

[Bernard Liengme]

Hopefully your numbers are generated by formulas. Then you can modify them
to read
=IF(your_formula<0,0,your_formula)
or
=IF(your_forumula,your_formula,0)
or
your_formula*(your_formula>0)

Or, if it is only for appearance and no further calculations are involved,
use a custom format like
#,"0","0"

best wishes

 

[Jon]

I've tried that and the cell goes blank
--
Jon

Hi Jon,

Use this formula:
=IF("Cell Reference"<0,0,"Cell Reference")

 

[Sean Timmons]

If you look at the Formula bar, is the number 0 in there?

Ensure your Cell Format is set as Number, 0.

 

[Jon]

this is a summary page from a months worth of data ='April 15'!E26 is what
appears in the formula bar.
thanks

 

[Sean Timmons]

OK, copy/ paste special Values to see the actual value, then undo it so you
don't lose the formula. If there's a negative value in there normally,

=IF('April 15'!E26<0,0,'April 15'!E26)

Should work fine...

 

[BSc Chem Eng Rick]

The problem may be that your sheet shows zeros as blanks, do the following
Office button --> Excel Options --> Advanced --> Display options for this
worksheet --> Show a zero in cells that have zero value.

 

[Jon]

no joy....I have an actual value show with copy/ paste special Values but
when i put in =IF('April 15'!E26<0,0,'April 15'!E26) it comes up
blank.......sigh
thanks

 

[Jon]

Office button???
--
Jon

The problem may be that your sheet shows zeros as blanks, do the following
Office button --> Excel Options --> Advanced --> Display options for this
worksheet --> Show a zero in cells that have zero value.

 

[Jon]

Thanks, I've tried these and they just come back blank. In other cells when
they are zero's they come back as a zero value.

 

[Jon]

Thanks,...tried that. It fills in all the blanks but still leaves that one
cell with a negative number

 

[Jacob Skaria]

Jon, I am not sure whether you have tried the suggestion of custom formatting
the cells to

General;"0";0
(This will display 0 for -ve values and 0's)

General;"0";""
(The above will display 0 for -ve values and blank for 0's)