I need a cell to read 69¢ if the amount is below a dollar, but $1.69 if the
amount exceeds a dollar. I know how to do either in FORMAT CELLS, but how do
I get Excel to see and format the cell based on this criteria?

Thank you in advance.

Not quite, but as close as I can get

[<1].00c;$#,##0.00

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Montana" wrote in message
...
I need a cell to read 69¢ if the amount is below a dollar, but $1.69 if the
amount exceeds a dollar. I know how to do either in FORMAT CELLS, but how
do
I get Excel to see and format the cell based on this criteria?

Thank you in advance.

Are you talking about the SAME cell as the original value is entered in, or
another one to return the preset results? Also, what result do you expect to
see when the value is EXACTLY one dollar?

Vaya con Dios,
Chuck, CABGx3


"Montana" wrote:

I need a cell to read 69¢ if the amount is below a dollar, but $1.69 if the
amount exceeds a dollar. I know how to do either in FORMAT CELLS, but how do
I get Excel to see and format the cell based on this criteria?

Thank you in advance.

Not quite, but as close as I can get

[<1].00c;$#,##0.00

It looks like you can get the cent (¢) sign by typing Alt+0162 from the
number pad in place of typing the letter c; however, it looks like we are
stuck with that decimal point.

Rick

Yes, in the same cell. If the value is 99, I want it to show 99¢. If it is a
dollar, it should read $1.00, not 100¢

On Jan 15, 11:32 am, Montana
wrote:
Yes, in the same cell. If the value is 99, I want it to show 99¢. If it is a
dollar, it should read $1.00, not 100¢

It's not as elegant as building a custom format, but you could hide
the data and use a formula to display the data in the way you want it.
Here's an ugly IF function nest that would do it assuming your number
is in D10 and the cent symbol is in G5:

=IF(D10<1,(D10*100)&$G$5,IF(MOD(D10*100,100)=0,"$" &D10&".
00",IF(MOD(D10*100,10)=0,"$"&D10&"0","$"&D10)))

The two mod bits are to deal with even dollar amounts (the first one)
and even tens where Excel likes to trunc the zeros off (note that
formatting the original numbers with 2 decimals of display wouldn't
solve this particular issue).
You could also use CHAR(162) for the cent sign instead.