Calculating value needed to reach a certain % goal

Hello,

Thanks in advance to anybody who can assist with this. I have tried several things to make it work and even tried goal seeker but it's not what i am looking for. I have attached a test spread sheet to give an idea and for the example. I use a spread sheet to track surveys that come in on employees. On my spreadsheet i have it broke down by office overall, by team , and by individual employee. What i need help with will pertain to the overall average by office.

I am trying to take the number (overall average of the office) for example cell "V4" on sheet 1 (NPS by Office). I want to know if 1 = 100 (our surveys only come in pass, passive, or detractor,ie: 100,0,-100) how many more surveys will be needed without any passives or fails (hence 1 = 100) to reach the goal of 55.00%. I would like for this answer to appear in sheet 3 (NPS trend and bottom 3) in cell "F5".

Summary Example:
Current score : 38.46% (cell Sheet1 V4)
surveys needed for goal : 5 (1 =100 , 500 total , gives score of 55.56%)
displays "5" in sheet 3 cell "F5"

Try

=ROUNDUP(55/(COUNTIF('NPS BY OFFICE'!$M$4:$M$51,"<>")+COUNTIF('NPS BY OFFICE'!$Q$4:$Q$51,"<>")),0)

@JohnTopley,

Thanks, while yes it seems to work, at least calculate the number 5, when i try to apply it to the other offices it is not calculating properly. For instance if i change the values to the Lafayette team which is above goal already i would like it to show a "0" or worst case even a -# showing how far over goal they are, however it is giving me a "2". Even if i took 2 surveys off it is still above goal so it confuses me on that part. Also when adding to the new orleans team it gives me a "1" but when adding 1 survey to the score it doesn't equal 55%. My numbers may vary slightly from yours as i applied this to the updated spreadsheet through today, and it is slightly updated compared to the test i attached originally but to me should be same concept and just appears that it is not working the way i need it to.

Thanks
Jay

The first issue is resolved by testing if %> 55:

=IF('NPS BY OFFICE'!J4<0.55,ROUNDUP(55/(COUNTIF('NPS BY OFFICE'!$A$4:$A$51,"<>")+COUNTIF('NPS BY OFFICE'!$E$4:$E$51,"<>")),0),0)

However, there is obviously a flaw in my mathematics as it does give an incorrect result for New Orleans. I'll have another think but maybe one of our mathematicians will respond.

Thanks i will give this a shot and see how it works and will report back.