if formula = negative #, then insert a word

[Guest]

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?

 

[Harlan Grove]

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

 

[Guest]

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

 

[Guest]

How do I get rid of the "-" in this case?

Nicole L. wrote...

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

 

[Peo Sjoblom]

Try

#,##0;" overage";0;@

 

[Peo Sjoblom]

Just the minus?

#,##0;#,##0" overage";0;@

 

[Guest]

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.

 

[Guest]

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]

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!

 

[Guest]

You're right. I thought I'd tried it but I did the wrong test.
Thank you! Thank you!

 

[Gord Dibben]

Nicole

=IF(SUM(C1:F1)<0,"overage",SUM(C1:F1))


Gord Dibben Excel MVP