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