MS Excel geeks, can you help? - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HUKD, you accept our cookie and privacy policy.
Get the HUKD app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

MS Excel geeks, can you help?

£0.00 @
Hope i explain this okay. I have a formula that's creating a percentage, so one figure divided by another, with the cell formatted as %. The formula is also an If statement and I want it to sa…
Dantooine Avatar
1m, 1w agoPosted 1 month, 1 week ago
Hope i explain this okay.

I have a formula that's creating a percentage, so one figure divided by another, with the cell formatted as %.

The formula is also an If statement and I want it to say 'if the % is below a certain number then show the % with a * after it, e.g. 45%*.

The formula is fine, but I can't figure out how to put the * on then end.

I tried concatenating, so 'cell&"*"' , but that then doesn't display the result as a %, just as a decimal, so instead of 45%, it gives me 0.4512345*.

Any ideas anyone??
Dantooine Avatar
1m, 1w agoPosted 1 month, 1 week ago
Options

All Responses

(15) Jump to unreadPost an answer
Responses/page:
#1
You can go to Format Cell, then under Number tab, pick 'Custom' Category. Then go down to where the have the suffixes like "mm2" and so on. Pick one of those are replace the 'mm2' with '*'. I think that's what you're asking.
#2
My inelegant solution is 'ROUND(cell*100,0)&"%*"'. However I suspect there is a more intelligent way but I do not know it.
#3
don't set it as a percentage put it as an integer then put the % and * to follow using &
#4
lol big foot beat me to it by a few seconds
#5
Dantooine
Hope i explain this okay.
I have a formula that's creating a percentage, so one figure divided by another, with the cell formatted as %.
The formula is also an If statement and I want it to say 'if the % is below a certain number then show the % with a * after it, e.g. 45%*.
The formula is fine, but I can't figure out how to put the * on then end.
I tried concatenating, so 'cell&"*"' , but that then doesn't display the result as a %, just as a decimal, so instead of 45%, it gives me 0.4512345*.
Any ideas anyone??

If your calculation was (for example) the result of 1 divided by 2, then this formula may be used:

=IF(1/2<0.6,TEXT(1/2,"0%\*"),"Other answer")

If the result of the formula is less than 0.6 (i.e. 60%), then the result is shown as a percentage (with a trailing % symbol and asterisk character).

Otherwise, the "Other answer" text represents what you wish to display if the result is not less than 60%.
#6
llocou
You can go to Format Cell, then under Number tab, pick 'Custom' Category. Then go down to where the have the suffixes like "mm2" and so on. Pick one of those are replace the 'mm2' with '*'. I think that's what you're asking.
That would format the cell no matter what, I just want the * there as the result of the if statement, so it wouldn't always be there
#7
fanpages
Dantooine
Hope i explain this okay.
I have a formula that's creating a percentage, so one figure divided by another, with the cell formatted as %.
The formula is also an If statement and I want it to say 'if the % is below a certain number then show the % with a * after it, e.g. 45%*.
The formula is fine, but I can't figure out how to put the * on then end.
I tried concatenating, so 'cell&"*"' , but that then doesn't display the result as a %, just as a decimal, so instead of 45%, it gives me 0.4512345*.
Any ideas anyone??
If your calculation was (for example) the result of 1 divided by 2, then this formula may be used:

=IF(1/2<0.6,TEXT(1/2,"0%\*"),"Other answer")

If the result of the formula is less than 0.6 (i.e. 60%), then the result is shown as a percentage (with a trailing % symbol and asterisk character).

Otherwise, the "Other answer" text represents what you wish to display if the result is not less than 60%.
What is the \ after the % ?
Doesn't seem to work.
#8
Thanks all for the replies, not there yet!
#9
bigfoot100
My inelegant solution is 'ROUND(cell*100,0)&"%*"'. However I suspect there is a more intelligent way but I do not know it.
GOing home but will try this tomorrow! :)
#10
Conditional formatting?
#11
fanpages
If your calculation was (for example) the result of 1 divided by 2, then this formula may be used:
=IF(1/2<0.6,TEXT(1/2,"0%\*"),"Other answer")

If the result of the formula is less than 0.6 (i.e. 60%), then the result is shown as a percentage (with a trailing % symbol and asterisk character).

Otherwise, the "Other answer" text represents what you wish to display if the result is not less than 60%.
Dantooine
What is the \ after the % ?
Doesn't seem to work.

It does work!

The \ before any character uses the exact (explicit) character in an "escaped" form.

http://i1152.photobucket.com/albums/p490/_fanpages/50_zpsmsmzlmb7.jpg

Look at it again tomorrow when you have more time.
#12
vij_chauhan
don't set it as a percentage put it as an integer then put the % and * to follow using &
Bigfoot wins! Thanks so much! :)
#13
This is complete formula
=IF(cell<0.65,ROUND(cell*100,0)&"%*",ROUND(cell*100,0)&"%")

Replace cell and 0.65 with your values
This is for display purposes only. It will not allow further calculations on result.
Happy trails
#14
fanpages
fanpages
If your calculation was (for example) the result of 1 divided by 2, then this formula may be used:
=IF(1/2<0.6,TEXT(1/2,"0%\*"),"Other answer")
If the result of the formula is less than 0.6 (i.e. 60%), then the result is shown as a percentage (with a trailing % symbol and asterisk character).
Otherwise, the "Other answer" text represents what you wish to display if the result is not less than 60%.
Dantooine
What is the \ after the % ?
Doesn't seem to work.
It does work!
The \ before any character uses the exact (explicit) character in an "escaped" form.http://i1152.photobucket.com/albums/p490/_fanpages/50_zpsmsmzlmb7.jpg
Look at it again tomorrow when you have more time.
Interesting, not seen the use of the \ like that will give it a try too. Thanks
#15
fanpages
Dantooine
Bigfoot wins! Thanks so much! :)
You're welcome.
I did thank everyone earlier

Post an Answer

You don't need an account to leave a response. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Looking for Twitter login?
Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!