Home |
Search |
Today's Posts |
#1
|
|||
|
|||
if formula = negative #, then insert a word
I have a cell (G1) that is the sum or some other calculation of other cells
(SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? |
#2
|
|||
|
|||
Nicole L. wrote...
I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#3
|
|||
|
|||
THanks - that SHOULD work but how can I remove the negative (-)?
I think there must be some other way, some formula... like an IF, then statement. Do you know of something like that? Thanks "Harlan Grove" wrote: Nicole L. wrote... I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#4
|
|||
|
|||
How do I get rid of the "-" in this case?
"Harlan Grove" wrote: Nicole L. wrote... I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#5
|
|||
|
|||
Try
#,##0;" overage";0;@ -- Regards, Peo Sjoblom "Nicole L." wrote in message ... THanks - that SHOULD work but how can I remove the negative (-)? I think there must be some other way, some formula... like an IF, then statement. Do you know of something like that? Thanks "Harlan Grove" wrote: Nicole L. wrote... I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#6
|
|||
|
|||
Just the minus?
#,##0;#,##0" overage";0;@ -- Regards, Peo Sjoblom "Nicole L." wrote in message ... How do I get rid of the "-" in this case? "Harlan Grove" wrote: Nicole L. wrote... I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#7
|
|||
|
|||
I'm using the custom format: #,##0;-#,##0" over est.";0;@
But when the number ends up being a negative, that's the only time I want this to show but I'd want it to show the number (not as a negative) plus the words "over est." (over estimate) So, if the calc. came to -4, I'd want the cell to show "4 over est." How can I do this? Right now, it's reading "-4 over est." - yuck! Thank you. "Peo Sjoblom" wrote: Try #,##0;" overage";0;@ -- Regards, Peo Sjoblom "Nicole L." wrote in message ... THanks - that SHOULD work but how can I remove the negative (-)? I think there must be some other way, some formula... like an IF, then statement. Do you know of something like that? Thanks "Harlan Grove" wrote: Nicole L. wrote... I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#8
|
|||
|
|||
But I only want that text to appear when the results are a negative.
So, I want to format or put a formula on a column of cells that have calculations in them (subtractions, mainly)... but in the rare case that the calc comes out with a negative, I want the number (without a minus sign) and the text "over est." to appear in that cell. I don't think just formatting the cell will actually do it. I'm thinking it's got to be a formula but I'm just so new to formulas. I've searched thru all postings having anything to do with Negatives to Positives to inserting text, etc... but nothing was even close. You guys have gotten the closest but it's still not doing what I know it can do and what I need it to do. Any more ideas? pointers? Maybe I'll have to skip the whole idea of the text and maybe just add some formatting if it's negative -- like a flashy color or something... I dunno. I just figured there's GOT to be a way! "Peo Sjoblom" wrote: Just the minus? #,##0;#,##0" overage";0;@ -- Regards, Peo Sjoblom "Nicole L." wrote in message ... How do I get rid of the "-" in this case? "Harlan Grove" wrote: Nicole L. wrote... I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#9
|
|||
|
|||
That is what it does
#,##0;#,##0" overage";0;@ will only show the number (without minus) and text if G1<0 otherwise it'll only show the number try it! -- Regards, Peo Sjoblom "Nicole L." wrote in message ... But I only want that text to appear when the results are a negative. So, I want to format or put a formula on a column of cells that have calculations in them (subtractions, mainly)... but in the rare case that the calc comes out with a negative, I want the number (without a minus sign) and the text "over est." to appear in that cell. I don't think just formatting the cell will actually do it. I'm thinking it's got to be a formula but I'm just so new to formulas. I've searched thru all postings having anything to do with Negatives to Positives to inserting text, etc... but nothing was even close. You guys have gotten the closest but it's still not doing what I know it can do and what I need it to do. Any more ideas? pointers? Maybe I'll have to skip the whole idea of the text and maybe just add some formatting if it's negative -- like a flashy color or something... I dunno. I just figured there's GOT to be a way! "Peo Sjoblom" wrote: Just the minus? #,##0;#,##0" overage";0;@ -- Regards, Peo Sjoblom "Nicole L." wrote in message ... How do I get rid of the "-" in this case? "Harlan Grove" wrote: Nicole L. wrote... I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#10
|
|||
|
|||
You're right. I thought I'd tried it but I did the wrong test.
Thank you! Thank you! "Peo Sjoblom" wrote: That is what it does #,##0;#,##0" overage";0;@ will only show the number (without minus) and text if G1<0 otherwise it'll only show the number try it! -- Regards, Peo Sjoblom "Nicole L." wrote in message ... But I only want that text to appear when the results are a negative. So, I want to format or put a formula on a column of cells that have calculations in them (subtractions, mainly)... but in the rare case that the calc comes out with a negative, I want the number (without a minus sign) and the text "over est." to appear in that cell. I don't think just formatting the cell will actually do it. I'm thinking it's got to be a formula but I'm just so new to formulas. I've searched thru all postings having anything to do with Negatives to Positives to inserting text, etc... but nothing was even close. You guys have gotten the closest but it's still not doing what I know it can do and what I need it to do. Any more ideas? pointers? Maybe I'll have to skip the whole idea of the text and maybe just add some formatting if it's negative -- like a flashy color or something... I dunno. I just figured there's GOT to be a way! "Peo Sjoblom" wrote: Just the minus? #,##0;#,##0" overage";0;@ -- Regards, Peo Sjoblom "Nicole L." wrote in message ... How do I get rid of the "-" in this case? "Harlan Grove" wrote: Nicole L. wrote... I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? If you just want negative numbers to display differently, use a custom number format. Format Cells, Number tab, select Custom from the Category list, then in the type field enter #,##0;-#,##0" overage";0;@ |
#11
|
|||
|
|||
Nicole
=IF(SUM(C1:F1)<0,"overage",SUM(C1:F1)) Gord Dibben Excel MVP On Fri, 4 Feb 2005 11:39:08 -0800, Nicole L. wrote: I have a cell (G1) that is the sum or some other calculation of other cells (SUM(C1:F1))... What formula do I use to say that IF that calculation turns out to be a negative, I'd like a word added, such as "overage" or something? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenging Charting | Charts and Charting in Excel | |||
How to insert a complex formula in a cell with VBA | Excel Worksheet Functions | |||
How do I replace a negative number at the end of a formula with a. | Excel Discussion (Misc queries) | |||
Help, insert a word document contents into excel tab? | Excel Discussion (Misc queries) | |||
insert multiple page Word document into Excel | Excel Discussion (Misc queries) |