• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format Cells to Percentage ---- Not Working....

PP3321

PP3321

Active Member
I have in-cell progress chart with the formula like below.

Code: 
=REPT("|",A1)&A1.

I want to change this format to % but it is not working...

Thanking you in advance...
 

Attachments

  • Chandoo Forum.xlsx
    11 KB · Views: 7
@NARAYANK991
Thank you so much for this!
If you do not mind, could you please explain why we need to do text()?

I am guessing if I multiple by 10, it becomes number?
But then I am repeating "|" by that number, which makes it text?

*I changed to General and it worked too.
screenshot.PNG
 
Hi ,

If we have a value such as 30% in a cell , say A2 , the % symbol appears in the cell only as a formatted display. The value contained in the cell is only the decimal value 0.3

Thus , to display the underlying value of 0.3 as 30% , you need to format the cell as Percentage.

So when you reference a cell which displays 30% , you are actually referencing the value of 0.3 ; now , because you have concatenated the value of 0.3 with other characters , the entire contents of the cell are already text and in a cell which contains text data , other formats cannot be applied.

Thus , to display the overall text string with the 0.3 as 30% cannot be done by formatting the cell as Percentage ; the percent symbol has to be introduced by means of the formula itself. This can be done by using the TEXT function.

Narayan
 
A modified to Narayan's formula as in :

=REPT("|",A2*30)&" "&TEXT(A2, "0%")

1] Since "Labeled Bar" used for illustration only, advised to use 30 instead of 10.

2] Add a Space between the bar and %.

3] Advice to use "Arial" font instead of "Calibri" font.

4] Please refer to the attached file.

Regards
Bosco
 

Attachments

  • LabelBar.xlsx
    11.2 KB · Views: 9
Last edited:
@bosco_yip
Thank you! Visualization looks much better!

I used Playbill as Chandoo Blog taught me how to do this. But as I add the number in the same cell, Playbill is not good. Now I think Arial is better option.
 
shrivallabha said:
One more variation would be:
=REPLACE(REPT(" ",32),1,LEN(REPT("o",A2*30)),REPT("o",A2*30))&TEXT(A2, "0%")

Font to be used "Courier New" which is Fixed Pitch font. It will place value labels uniformly!
Click to expand...

what's the meaning of REPLACE(REPT(" ",32) with REPT(" ",32)
 
Hey FIFA,

Firstly welcome to our family!.

REPT function is use for repeats text a given number of times

REPT(" ",32)

with reference of above formula, the REPT function create " " 32 times.

REPLACE(REPT(" ",32)

with reference of above formula, REPT(" ",32) is the first argument of REPLACE function.



FIFA said:
what's the meaning of REPLACE(REPT(" ",32) with REPT(" ",32)
Click to expand...
FIFA said:
what's the meaning of REPLACE(REPT(" ",32) with REPT(" ",32)
Click to expand...
 
xlstime said:
Hey FIFA,

Firstly welcome to our family!.

REPT function is use for repeats text a given number of times

REPT(" ",32)

with reference of above formula, the REPT function create " " 32 times.

REPLACE(REPT(" ",32)

with reference of above formula, REPT(" ",32) is the first argument of REPLACE function.
Click to expand...
thanks for sharing the use of rept function

but in the Attached Files the a2 cell,b2 cell has no 32 times " ",why?
 
Which attachment you are referring, unable to find, can you please post complete formula or attach sample.


FIFA said:
thanks for sharing the use of rept function

but in the Attached Files the a2 cell,b2 cell has no 32 times " ",why?
Click to expand...
 
Hi ,

The first thing to understand is that the REPLACE function is creating a text string full of spaces , with a length of 32 characters.

Next , within these 32 space characters , as many characters as are required are replaced by any symbol of your choice ; Shrivallabha has chosen the letter o.

Since a percentage is a numeric value which varies between 0 and 1 , with 1 the equivalent of 100 % , entering 1 or 100 % will result in 30 o characters.

10 % will result in 0.1 * 30 = 3 o characters

20 % will result in 0.2 * 30 = 6 o characters

and so on.

Thereafter , the actual value is displayed using the TEXT function and a format string of 0%.

All this is to ensure that in a column of such displays , the percentage values are all lined up on the right.

Narayan
 
Hi ,

The formula can be simplified to :

=REPLACE(REPT(" ",32),1,A2*30,REPT("o",A2*30))&TEXT(A2, "0%")

since the construct :

LEN(REPT("o",A2*30))

is always going to be equal to :

A2 * 30

To cater to negative values also , we can use :

=REPLACE(REPT(" ",32),1,ABS(A2*30),REPT("o",ABS(A2*30)))&TEXT(A2, "0%")

Narayan
 
You must log in or register to reply here.
Back
Top