Finetune formula to get currency format?

[StargateFan]

I've managed to put together two different formulas from two different
sources and it seems to be working okay except in terms of the format
of the numbers.

The formula is this:

=IF(G2="?",TEXT(G2,"$0")&" ttl unknown",G2*I2)

The "$0" in there is a remnant from the original. I don't know what
to do with it so I've left it in.

The purpose of this formula is to deal with situations where a list
price is unknown and a user puts a question mark in G2. This formula
goes into the totals column but there is no total known so the result
will say:

"? ttl unknown"

Yet when there _is_ a price in G2, then the formula should return a
product, derived from G2 x I2.
And when that is the case, rather than see "? ttl unknown" in the cell
(without quotation marks, of course) it should see a dollar amount
with 2 decimal places.

A second column deals with a total after a discount and it looks like
this:

=IF(G2="?",TEXT(G2,"$0")&" ttl price unknown",J2*(O1/100))

Once I know how to display currency in the formula when there is a
dollar amount, I can then modify this second formula.

How can I achieve this through modifying the formula above?

Thanks! D

 

[Gord Dibben]

Do you want to be able to calculate with the results?

I would use =IF(G2="?","$0 ttl unknown",G2*I2)

Then format the cell as Currency.

For a Text only result use

=IF(G2="?","$0 ttl unknown",TEXT(G2*I2,"$#,##0.00"))


Gord Dibben MS Excel MVP