IF percentage statement

​​Hi, I have a list of percentages in row A, and I want to create a formula that, whenever the percentages don't add up to 100%, an even amount is added to each cell in Column A, and the number is returned in row B.

Basically, I want it to add a small amount to %11.77, %13.24, %12.93 etc., to make the total (currently %87.76) into %100

A
11.77% 13.24% 12.93% 13.84% 7.84% 7.69% 5.57% 6.22% 3.21% 2.95% 2.50%

Total 87.76%

Thank you in advance for you help!

If I understand your question correctly, you want to:

1. Input Row 1, assuming that Row 1 should eventually add up to 100%

2. Sum Row 1 (87.76% in this case)

3. Get the inverse of Row 1 and 100% (12.34% in this case)

4. Divide the inverse by the # of cells summed

5. Add this new average value to each cell in Row 1, and display the output in Row 2.

If that understanding is correct, here's what I'd do:

• Start creating a formula to calculate the value to be added to each cell.

Example: =(1-SUM(A1:Z1))/COUNTA(A1:Z1) , where 1 = 100%, SUM(A1:Z1) is adding up the entire Row 1, and COUNTA(A1:Z1) will count the number of non-blank cells, so it divides by the correct # of cells summed. I placed mine in M6

• Create a second formula in Row 2 to add this value

Example: =A1+$M$6 , =B1+$M$6, etc and repeat for all the cells in Row 2. If you cannot use a helper cell you can also replace $M$6 with the formula above.

• If you're worried about rounding, and must have the displayed numbers equal to 100%, then you can add two details. One is to add rounding to the value calculation to the relevant decimal. Two is to add condition checks to the final cells that compensate for rounding issues in averages.

Example #1: =ROUND((1-SUM(A1:Z1))/COUNTA(A1:Z1),4) , where the original formula has been surrounded by ROUND( ... , 4) , which is the precision you provided in your examples

Example #2: =IF(SUM(A2:J2,K1+$M$6) <> 1,1-SUM(A2:J2), K1+$M$6) where K1 was the final cell of values in Row 1, $M$6 is the rounded formula, and A2:J2 are the output cells in Row 2 that have values.

• Be fully aware that this last message turns the final cell into a basket to catch all the rounding errors, and is not recommended in most situations.