| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas dollars and formula %
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 ..... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formulas dollars and formula %
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 -- Wag more, bark less "Wanna Learn" wrote: 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 ..... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formulas dollars and formula %
=IF(J4=5,TEXT(K4,"$#,##0.00"),IF(J4=1,TEXT((100-K4)/100,"00 %"),""))
-- John C "Wanna Learn" wrote: 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 ..... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formulas dollars and formula %
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.... -- Wag more, bark less "Wanna Learn" wrote: 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 ..... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formulas dollars and formula %
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. -- John C "Brad" wrote: 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.... -- Wag more, bark less "Wanna Learn" wrote: 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 ..... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formulas dollars and formula %
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.... -- Wag more, bark less "John C" wrote: 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. -- John C "Brad" wrote: 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.... -- Wag more, bark less "Wanna Learn" wrote: 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 ..... |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formulas dollars and formula %
I have xl2003, and it is not available. And the OP has xl2002.
-- John C "Brad" wrote: 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.... -- Wag more, bark less "John C" wrote: 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. -- John C "Brad" wrote: 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.... -- Wag more, bark less "Wanna Learn" wrote: 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 ..... |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formulas dollars and formula %
You are correct - you cannot change the number format with conditional
formating using Excel 2003. -- Wag more, bark less "John C" wrote: I have xl2003, and it is not available. And the OP has xl2002. -- John C "Brad" wrote: 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.... -- Wag more, bark less "John C" wrote: 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. -- John C "Brad" wrote: 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.... -- Wag more, bark less "Wanna Learn" wrote: 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 ..... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to round values from formulas to whole dollars | Excel Discussion (Misc queries) | |||
| convert dollars to pounds using a lookup formula | Excel Worksheet Functions | |||
| what is the formula from british pounds to American dollars | Excel Worksheet Functions | |||
| I wish to make a formula that adds dollars in 1 column if a date . | Excel Worksheet Functions | |||
| converting #dollars into word dollars? | Excel Worksheet Functions | |||
Linear Mode
