Negative

J

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
 
P

Pete_UK

Try this amendment to your existing formulae in those cells:

=MAX(0,existing_formula)

Hope this helps.

Pete
 
J

Jacob Skaria

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

General;"0";0
 
B

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
 
S

Sean Timmons

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

Ensure your Cell Format is set as Number, 0.
 
J

Jon

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

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...
 
B

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.
 
J

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
 
J

Jon

Office button???
--
Jon


BSc Chem Eng Rick said:
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.
 
J

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.
 
J

Jon

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top