SOLVED

Contributor

# Formula Help

I have the formula shown below which works but I need help with resulting in the proper percentage format.

The formula references cell D34 which has "18 Units" in the cell.  It is a mixed cell of numbers and text fore which I am extracting the numbers from.  The formula produces a result of "299".  So, 317 - D34(18) = "299". With this result of "299" I now need to divide 299 by 317 to get a percentage. 299/317= 316.9432177. I am trying to figure out what I need to do from here to get the result to read, 94.32%.

Can anyone assist me with this?

{=317-SUBSTITUTE(D34,RIGHT(D34,LEN(D34)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},D34),""))),"")/317}

Thank you.

Carl

8 Replies

# Re: Formula Help

{=(317-SUBSTITUTE(D34,RIGHT(D34,LEN(D34)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},D34),""))),""))/317}

You can also simplify by using the following formula

``=(317-VALUE(LEFT(D34,FIND(" ",D34,1)-1)))/317``

# Re: Formula Help

Thank you I tried your formula. It gives me my results but how can I format the percentage to say like 94.32%. You know rounding to the nearest 2 digits beyond the . (period)

# Re: Formula Help

Also I just discovered that if D34 does not have a space between the numbers and the letters it does not work. The result ends up with #Value! in the cell. So either formula which works, one that accounts for a space or one with no space, still needs to format the result as 94.32%. Can you assist me with the formatting of the result per the way I have shown??
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Formula Help

Remember if it worked please flag it as solved and hit the like button.

1 - A simple solution by formatting the cell as Percentage

2- Or wrap up the result with another formula

``=TEXT(PreviousFormula,"0.00%")``

# Re: Formula Help

I have applied this formula but I cannot get the beginning digits to strip away. What is wrong? What am I doing wrong? My result is 316.9432177. I cannot figure out how to get rid of the 316 in front of the number. I have tried different combinations of the "0.00%" but am not ending up with 94.32%. What am I doing wrong??? Even pressing the % button on the home page is not making this happen. I'm pulling out my hair on this.

Find attachment.

# Re: Formula Help

Ok thanks. Got it. Had an Close ) in the wrong place.

# Re: Formula Help

Good to hear that. Also remember if it worked please flag it as solved .