formulas dollars and formula %

[Wanna Learn]

Hello
Excel 2002 - involves 2 columns Column K and column J
If the value in K4 = 5 then whatever is in cell J4 in dollars
But if the value in K4 =1 then J4 – 100 in %
Example
Column J Column K Results should be
5 1375 $1,375
1 22 78%
Thanks in advance
I've have tried .....

 

[Brad]

Slightly confused with your example you state the K4 should equal 1 or 5, but
in your example the 1 and 5 appear in J4

Assuming the 1 and 5 are in J4 - Two ways to solve this
=(J4=5)*K4+(J4=1)*(1-k4/100) - you will have to use conditional formating to
change the "presentation" of the numbers

second way
=if(J4=5,K4,if(J4=1,1-k4/100,"not one or five")) - you will have to use
conditional formating to change the "presentation" of the numbers

 

[John C]

=IF(J4=5,TEXT(K4,"$#,##0.00"),IF(J4=1,TEXT((100-K4)/100,"00 %"),""))

 

[Brad]

John C comments on using the text will work as well as the conditional
formating - our formulas will yield the same results - although they look
slightly different....

 

[John C]

Conditional formatting changes the display of the cell, borders, font color,
background color, underlining, bold, etc. I am not sure where it can change
the presentation of the cell contents, as you suggest.

 

[Brad]

With excel 2007 conditional formating allows the changing of the presentaion
of cell contents (Just got done validating this) - I'm pretty sure that that
feature was in 2003 - Not sure if it was in prior versions - but based on
past postings - I would think that it would be there....

 

[John C]

I have xl2003, and it is not available. And the OP has xl2002.

 

[Brad]

You are correct - you cannot change the number format with conditional
formating using Excel 2003.