MS Excel geeks, can you help?

15 replies
Found 10th Jan
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??

15 Comments

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.

My inelegant solution is 'ROUND(cell*100,0)&"%*"'. However I suspect there is a more intelligent way but I do not know it.

don't set it as a percentage put it as an integer then put the % and * to follow using &

lol big foot beat me to it by a few seconds

Dantooine

Hope i explain this okay.I have a formula that's creating a percentage, … 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%.

Original Poster

llocou

You can go to Format Cell, then under Number tab, pick 'Custom' Category. … 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

Original Poster

fanpages

If your calculation was (for example) the result of 1 divided by 2, then … 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.

Original Poster

Thanks all for the replies, not there yet!

Original Poster

bigfoot100

My inelegant solution is 'ROUND(cell*100,0)&"%*"'. However I suspect … 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!

Conditional formatting?

fanpages

If your calculation was (for example) the result of 1 divided by 2, then … 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.

Original Poster

vij_chauhan

don't set it as a percentage put it as an integer then put the % and * to … don't set it as a percentage put it as an integer then put the % and * to follow using &


Bigfoot wins! Thanks so much!

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

Original Poster

fanpages

If your calculation was (for example) the result of 1 divided by 2, then … 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.


Interesting, not seen the use of the \ like that will give it a try too. Thanks

Original Poster

fanpages

You're welcome.


I did thank everyone earlier
Post a comment
Avatar
@
    Text
    Top Discussions
    1. Topcashback Trick or Treat competition61630
    2. Chat magazine issue 4312
    3. Win a £100 Amazon Voucher From LoveMyVouchers22
    4. Magazine competitions - Issue 41 @ tvchoicemagazine.co.uk1717

    See more discussions