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?

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

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

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

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

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

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

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

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

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

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?