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
that appear to come out as zero's
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Pete_UK
Try this amendment to your existing formulae in those cells:
=MAX(0,existing_formula)
Hope this helps.
Pete
=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
General;"0";0
B
BSc Chem Eng Rick
Hi Jon,
Use this formula:
=IF("Cell Reference"<0,0,"Cell Reference")
Use this formula:
=IF("Cell Reference"<0,0,"Cell Reference")
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
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
J
Jon
I've tried that and the cell goes blank
--
Jon
--
Jon
BSc Chem Eng Rick said:Hi Jon,
Use this formula:
=IF("Cell Reference"<0,0,"Cell Reference")
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.
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
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...
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.
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
when i put in =IF('April 15'!E26<0,0,'April 15'!E26) it comes up
blank.......sigh
thanks
J
Jon
Office button???
--
Jon
--
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.
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
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)
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)