The table below shows that we have five questions answered by our customer. If they rated "9" or "10", it is considered a Top Box Score, and its total is divided by the number of scored questions to get the CSAT percentage. Is it possible to have a formula under columns 52%, 68%, and 75% to determine how many more questions must be scored as 9s or 10s to reach that said percentages? What I currently do is expand the range of cells in the questions (instead of A2:E6 I use A2:A20) then add like 9s and 10s to check it manually. Q1 Q2 Q3 Q4 Q5 Top Box Survey CSAT 52% 68% 75% 9 9 9 9 9 11 25 44.00% 1 1 1 1 1 3 7 8 6 5 4 3 6 4 9 10 10 10 10 10

message edited by ChGatsby

You didn't include Row numbers or Column letters, so we have to guess as to where your data is. Assuming your data starts in A1, which means that 52% is in I1, put this in I2 and drag it to K2:

=ROUNDUP((I1*$G$2)-$F$2,0)

The ROUNDUP function ensures that whole numbers are returned. That also means that the percentage might be "overshot". e.g. for your example, you would need 7.75 Top scores to hit exactly 75%. My suggestion will return 8.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll