Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple formats in a cell
Can anyone offer a solution to the issue of allowing a cell to display a
value in two formats? I have a spreadsheet and in a cell I have a lookup which checks a table depending on what is selected from a list, the table has two different values Number and Percentage, these values are pulled into a summary screen to complete a sentance. I can only get the value to display as either a number or a percentage. Any suggestions please? Thanks, Gridlock. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple formats in a cell
Hi,
You could try this. This assumed any value less that 1 as a percentage. Also, the output will be a text value and not a number. =IF(D23<1,TEXT(D23,"0.00%"),D23) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Gridlock" wrote in message ... Can anyone offer a solution to the issue of allowing a cell to display a value in two formats? I have a spreadsheet and in a cell I have a lookup which checks a table depending on what is selected from a list, the table has two different values Number and Percentage, these values are pulled into a summary screen to complete a sentance. I can only get the value to display as either a number or a percentage. Any suggestions please? Thanks, Gridlock. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple formats in a cell
On Wed, 18 Nov 2009 01:01:01 -0800, Gridlock
wrote: Can anyone offer a solution to the issue of allowing a cell to display a value in two formats? I have a spreadsheet and in a cell I have a lookup which checks a table depending on what is selected from a list, the table has two different values Number and Percentage, these values are pulled into a summary screen to complete a sentance. I can only get the value to display as either a number or a percentage. Any suggestions please? Thanks, Gridlock. It is hard to give specifics without knowing more. You can't just copy the format using worksheet functions. However, you could use the CELL worksheet function to determine whether the original cell is formatted as Percentage; then use the TEXT worksheet function to display this as desired. For example, assuming that your cells will be displayed as either a Percentage with 2 decimal places; or a number with 2 decimal places: =TEXT(A1,IF(CELL("format",A1)="P2","0.00%","0.00") ) Look at HELP for the CELL worksheet function for other options. Be aware that merely changing the format of a cell does not trigger a calculation event, so the CELL worksheet function may not update until some value changes. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple formats in a cell | Excel Worksheet Functions | |||
Multiple Formats per Cell | Excel Worksheet Functions | |||
Multiple formats in a single cell with multiple formulas | Excel Worksheet Functions | |||
Multiple formats in the same cell | Excel Worksheet Functions | |||
Multiple formats within a cell | Excel Discussion (Misc queries) |