Multiple formats in a cell

[Gridlock]

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.

 

[Ashish Mathur]

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

 

[Ron Rosenfeld]

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