SOLVED

Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2842854%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842854%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20formula%20shown%20below%20which%20works%20but%20I%20need%20help%20with%20resulting%20in%20the%20proper%20percentage%20format.%3C%2FP%3E%3CP%3EThe%20formula%20references%20cell%20D34%20which%20has%20%2218%20Units%22%20in%20the%20cell.%26nbsp%3B%20It%20is%20a%20mixed%20cell%20of%20numbers%20and%20text%20fore%20which%20I%20am%20extracting%20the%20numbers%20from.%26nbsp%3B%20The%20formula%20produces%20a%20result%20of%20%22299%22.%26nbsp%3B%20So%2C%20317%20-%20D34(18)%20%3D%20%22299%22.%20With%20this%20result%20of%20%22299%22%20I%20now%20need%20to%20divide%20299%20by%20317%20to%20get%20a%20percentage.%20299%2F317%3D%20316.9432177.%20I%20am%20trying%20to%20figure%20out%20what%20I%20need%20to%20do%20from%20here%20to%20get%20the%20result%20to%20read%2C%2094.32%25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20assist%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3D317-SUBSTITUTE(D34%2CRIGHT(D34%2CLEN(D34)-MAX(IFERROR(FIND(%7B0%2C1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%7D%2CD34)%2C%22%22)))%2C%22%22)%2F317%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECarl%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2842854%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843137%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843137%22%20slang%3D%22en-US%22%3EThank%20you%20I%20tried%20your%20formula.%20It%20gives%20me%20my%20results%20but%20how%20can%20I%20format%20the%20percentage%20to%20say%20like%2094.32%25.%20You%20know%20rounding%20to%20the%20nearest%202%20digits%20beyond%20the%20.%20(period)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843352%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1171720%22%20target%3D%22_blank%22%3E%40Carl_61%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20attachment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843474%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843474%22%20slang%3D%22en-US%22%3EOk%20thanks.%20Got%20it.%20Had%20an%20Close%20)%20in%20the%20wrong%20place.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843479%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843479%22%20slang%3D%22en-US%22%3EGood%20to%20hear%20that.%20Also%20remember%20if%20it%20worked%20please%20flag%20it%20as%20solved%20.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843031%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1171720%22%20target%3D%22_blank%22%3E%40Carl_61%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3D%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E(%3C%2FFONT%3E%3C%2FSTRONG%3E317-SUBSTITUTE(D34%2CRIGHT(D34%2CLEN(D34)-MAX(IFERROR(FIND(%7B0%2C1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%7D%2CD34)%2C%22%22))%3CFONT%20color%3D%22%23000000%22%3E)%3C%2FFONT%3E%2C%22%22)%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E)%3C%2FFONT%3E%3C%2FSTRONG%3E%2F317%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20also%20simplify%20by%20using%20the%20following%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D(317-VALUE(LEFT(D34%2CFIND(%22%20%22%2CD34%2C1)-1)))%2F317%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843193%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843193%22%20slang%3D%22en-US%22%3EAlso%20I%20just%20discovered%20that%20if%20D34%20does%20not%20have%20a%20space%20between%20the%20numbers%20and%20the%20letters%20it%20does%20not%20work.%20The%20result%20ends%20up%20with%20%23Value!%20in%20the%20cell.%20So%20either%20formula%20which%20works%2C%20one%20that%20accounts%20for%20a%20space%20or%20one%20with%20no%20space%2C%20still%20needs%20to%20format%20the%20result%20as%2094.32%25.%20Can%20you%20assist%20me%20with%20the%20formatting%20of%20the%20result%20per%20the%20way%20I%20have%20shown%3F%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843224%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1171720%22%20target%3D%22_blank%22%3E%40Carl_61%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERemember%20if%20it%20worked%20please%20flag%20it%20as%20solved%20and%20hit%20the%20like%20button.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1%20-%20A%20simple%20solution%20by%20formatting%20the%20cell%20as%20Percentage%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JulianoPetrukio_0-1634150402510.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317141i3DBBBB844E95F744%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JulianoPetrukio_0-1634150402510.png%22%20alt%3D%22JulianoPetrukio_0-1634150402510.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E2-%20Or%20wrap%20up%20the%20result%20with%20another%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTEXT(PreviousFormula%2C%220.00%25%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2843328%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843328%22%20slang%3D%22en-US%22%3EI%20have%20applied%20this%20formula%20but%20I%20cannot%20get%20the%20beginning%20digits%20to%20strip%20away.%20What%20is%20wrong%3F%20What%20am%20I%20doing%20wrong%3F%20My%20result%20is%20316.9432177.%20I%20cannot%20figure%20out%20how%20to%20get%20rid%20of%20the%20316%20in%20front%20of%20the%20number.%20I%20have%20tried%20different%20combinations%20of%20the%20%220.00%25%22%20but%20am%20not%20ending%20up%20with%2094.32%25.%20What%20am%20I%20doing%20wrong%3F%3F%3F%20Even%20pressing%20the%20%25%20button%20on%20the%20home%20page%20is%20not%20making%20this%20happen.%20I'm%20pulling%20out%20my%20hair%20on%20this.%3C%2FLINGO-BODY%3E
Contributor

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

@Carl_61 

{=(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

 

 

 

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)
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

@Carl_61 

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

 

1 - A simple solution by formatting the cell as Percentage

JulianoPetrukio_0-1634150402510.png

2- Or wrap up the result with another formula

 

=TEXT(PreviousFormula,"0.00%")

 

 

 

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.

@Carl_61 

Find attachment.

Ok thanks. Got it. Had an Close ) in the wrong place.
Good to hear that. Also remember if it worked please flag it as solved .