CODEWITHSUNDEEP
excelformattingformulapercentage
cdfj
cdfj

Reputation: 165

Excel formula for linked value as percentage

Excel converts a percentage to a fraction when pasted as a link, e.g. 50% in referenced cell B2 in Sheet1 is shown as 0.5 in linked cell X10 in Sheet2.

However, instead of a percentage, some linked cells in Sheet1 contain text or are empty (there are many workbooks - Sheet1 is a questionnaire).

So a formula like =((('Sheet1'!B2)*100)& "%") & "" does not work for all cells, because the value might be text (gives a #VALUE error), and also & "" does not work for empty cells in this formula.

I need a formula to convert the linked fractions in Sheete to percentages in Sheet2. I have tried with ISNUMBER without success and also do not know how ISNUMBER could be made to work with a text value (yet another nested IF formula, I guess).

Can anyone point to a solution?

Thanks!

CJ

Upvotes: 0

Views: 1730

Answers (2)

JvdV
JvdV

Reputation: 75840

A simple conditional formatting example below:

Step 1

  • Rightclick cell A2 > Format cells > Number > Custom > 0;-0;;@
  • Type a 0 in cell B2 to test if zeros are no longer displayed

enter image description here enter image description here

Step 2

  • Type =B2 in cell A2
  • Type Test in cell B2 to check link and leave it

enter image description here

Step 3

  • Select cell A2 > Start > Conditional Formatting > New Rule > Use a Formula > =ISNUMBER(B2)
  • Format > Number > Percentage
  • Type 0,8 in cell B2 to test.

enter image description here

The conditional formatting rule will overwrite your custom formatting rule when the rule applies e.g. when cell B2 is a number!

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34180

I'm not sure if it is worth putting as an answer, but I don't see a problem with

=IF(ISNUMBER(Sheet1!B1),Sheet1!B1,"")

and format the range in Sheet2 starting with B1 as percentage with required number of decimal places.

For conditional formatting, I could only get it to work with

=NOT(ISNUMBER(Sheet1!B1))

and a format of ;;;

EDIT

If you want the text to appear, first formula should be

=IF(Sheet1!B1="","",Sheet1!B1)

or use ;;;@ in above formatting rule.

Upvotes: 0

Related Questions