| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
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_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
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
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
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
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'!E26<0,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
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
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 "Sean Timmons" wrote: 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... "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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
Office button???
-- Jon "BSc Chem Eng Rick" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
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 "Bernard Liengme" wrote: 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_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 . |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
Thanks,...tried that. It fills in all the blanks but still leaves that one
cell with a negative number -- Jon "מיכאל (מיקי) אבידן" wrote: 'Tools' 'Options...' 'View' tab. Micky "Jon" wrote: Office button??? -- Jon "BSc Chem Eng Rick" wrote: 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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Negative
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) -- Jacob (MVP - Excel) "Jon" wrote: 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 "Bernard Liengme" wrote: 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_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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing color of negative bar when using Invert if negative | Charts and Charting in Excel | |||
| I want to get negative hours if subtraction of hours is negative | Excel Worksheet Functions | |||
| Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
| Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
| 2003= negative number&2004= negative number How Do I Calculate gro | Excel Worksheet Functions | |||
Linear Mode
