|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
April 30th, 2010, 01:42 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Negative
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 -- Jon 
|
|
#2
April 30th, 2010, 01:45 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
Try this amendment to your existing formulae in those cells:
=MAX(0,existing_formula) Hope this helps. Pete On Apr 30, 1:42*pm, Jon wrote: 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 -- Jon
|
|
#3
April 30th, 2010, 01:49 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
Custom format the cells to the below (from menu FormatCellCustomType)
General;"0";0 -- Jacob (MVP - Excel) "Jon" wrote: 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 -- Jon
|
|
#4
April 30th, 2010, 01:51 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
Hi Jon,
Use this formula: =IF("Cell Reference"0,0,"Cell Reference") -- If this helps, please click "Yes" "Jon" wrote: 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 -- Jon
|
|
#5
April 30th, 2010, 01:53 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
Hopefully your numbers are generated by formulas. Then you can modify them
to read =IF(your_formula0,0,your_formula) or =IF(your_forumula,your_formula,0) or your_formula*(your_formula0) Or, if it is only for appearance and no further calculations are involved, use a custom format like #,"0","0" best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jon" wrote in message ... 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 -- Jon
|
|
#6
April 30th, 2010, 02:01 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
I've tried that and the cell goes blank
-- Jon "BSc Chem Eng Rick" wrote: Hi Jon, Use this formula: =IF("Cell Reference"0,0,"Cell Reference") -- If this helps, please click "Yes" "Jon" wrote: 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 -- Jon
|
|
#7
April 30th, 2010, 02:05 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
If you look at the Formula bar, is the number 0 in there?
Ensure your Cell Format is set as Number, 0. "Jon" wrote: I've tried that and the cell goes blank -- Jon "BSc Chem Eng Rick" wrote: Hi Jon, Use this formula: =IF("Cell Reference"0,0,"Cell Reference") -- If this helps, please click "Yes" "Jon" wrote: 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 -- Jon
|
|
#8
April 30th, 2010, 02:16 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
this is a summary page from a months worth of data ='April 15'!E26 is what
appears in the formula bar. thanks -- Jon "Sean Timmons" wrote: If you look at the Formula bar, is the number 0 in there? Ensure your Cell Format is set as Number, 0. "Jon" wrote: I've tried that and the cell goes blank -- Jon "BSc Chem Eng Rick" wrote: Hi Jon, Use this formula: =IF("Cell Reference"0,0,"Cell Reference") -- If this helps, please click "Yes" "Jon" wrote: 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 -- Jon
|
|
#9
April 30th, 2010, 02:20 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
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'!E260,0,'April 15'!E26) Should work fine... "Jon" wrote: this is a summary page from a months worth of data ='April 15'!E26 is what appears in the formula bar. thanks -- Jon "Sean Timmons" wrote: If you look at the Formula bar, is the number 0 in there? Ensure your Cell Format is set as Number, 0. "Jon" wrote: I've tried that and the cell goes blank -- Jon "BSc Chem Eng Rick" wrote: Hi Jon, Use this formula: =IF("Cell Reference"0,0,"Cell Reference") -- If this helps, please click "Yes" "Jon" wrote: 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 -- Jon
|
|
#10
April 30th, 2010, 02:22 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
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. -- If this helps, please click "Yes" "Jon" wrote: this is a summary page from a months worth of data ='April 15'!E26 is what appears in the formula bar. thanks -- Jon "Sean Timmons" wrote: If you look at the Formula bar, is the number 0 in there? Ensure your Cell Format is set as Number, 0. "Jon" wrote: I've tried that and the cell goes blank -- Jon "BSc Chem Eng Rick" wrote: Hi Jon, Use this formula: =IF("Cell Reference"0,0,"Cell Reference") -- If this helps, please click "Yes" "Jon" wrote: 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 -- Jon
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
