Dividing formulas is causing unintentional addition

[ollyhughes1982]

Hi,

I have the following two formulas:

A:
=ROWS('All Completed Runs - HPR'!C4:C63)-COUNTBLANK('All Completed Runs - HPR'!C4:C63)

B:
=COUNTIF('All Completed Runs - HPR'!A4:A63,"A")+COUNTIF('All Completed Runs - HPR'!A4:A63,"B")+COUNTIF('All Completed Runs - HPR'!A4:A63,"C")+COUNTIF('All Completed Runs - HPR'!A4:A63,"D")+COUNTIF('All Completed Runs - HPR'!A4:A63,"E")+COUNTIF('All Completed Runs - HPR'!A4:A63,"F")+COUNTIF('All Completed Runs - HPR'!A4:A63,"G")+COUNTIF('All Completed Runs - HPR'!A4:A63,"H")+COUNTIF('All Completed Runs - HPR'!A4:A63,"I")+COUNTIF('All Completed Runs - HPR'!A4:A63,"J")+COUNTIF('All Completed Runs - HPR'!A4:A63,"K")+COUNTIF('All Completed Runs - HPR'!A4:A63,"L")+COUNTIF('All Completed Runs - HPR'!A4:A63,"M")+COUNTIF('All Completed Runs - HPR'!A4:A63,"N")+COUNTIF('All Completed Runs - HPR'!A4:A63,"O")+COUNTIF('All Completed Runs - HPR'!A4:A63,"P")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Q")+COUNTIF('All Completed Runs - HPR'!A4:A63,"R")+COUNTIF('All Completed Runs - HPR'!A4:A63,"S")+COUNTIF('All Completed Runs - HPR'!A4:A63,"T")+COUNTIF('All Completed Runs - HPR'!A4:A63,"U")+COUNTIF('All Completed Runs - HPR'!A4:A63,"V")+COUNTIF('All Completed Runs - HPR'!A4:A63,"W")+COUNTIF('All Completed Runs - HPR'!A4:A63,"X")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Y")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Z")

They both work correctly, separately; Formula A’s result is 21 and formula B’s result is also 21, as I have completed all of the requirements. All I want to do now is add a percentage cell, so I want to divide formula A into formula B and get 1 (100%). I have tried to do this with the following formula:

=ROWS('All Completed Runs - HPR'!C4:C63)-COUNTBLANK('All Completed Runs - HPR'!C4:C63)/COUNTIF('All Completed Runs - HPR'!A4:A63,"A")+COUNTIF('All Completed Runs - HPR'!A4:A63,"B")+COUNTIF('All Completed Runs - HPR'!A4:A63,"C")+COUNTIF('All Completed Runs - HPR'!A4:A63,"D")+COUNTIF('All Completed Runs - HPR'!A4:A63,"E")+COUNTIF('All Completed Runs - HPR'!A4:A63,"F")+COUNTIF('All Completed Runs - HPR'!A4:A63,"G")+COUNTIF('All Completed Runs - HPR'!A4:A63,"H")+COUNTIF('All Completed Runs - HPR'!A4:A63,"I")+COUNTIF('All Completed Runs - HPR'!A4:A63,"J")+COUNTIF('All Completed Runs - HPR'!A4:A63,"K")+COUNTIF('All Completed Runs - HPR'!A4:A63,"L")+COUNTIF('All Completed Runs - HPR'!A4:A63,"M")+COUNTIF('All Completed Runs - HPR'!A4:A63,"N")+COUNTIF('All Completed Runs - HPR'!A4:A63,"O")+COUNTIF('All Completed Runs - HPR'!A4:A63,"P")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Q")+COUNTIF('All Completed Runs - HPR'!A4:A63,"R")+COUNTIF('All Completed Runs - HPR'!A4:A63,"S")+COUNTIF('All Completed Runs - HPR'!A4:A63,"T")+COUNTIF('All Completed Runs - HPR'!A4:A63,"U")+COUNTIF('All Completed Runs - HPR'!A4:A63,"V")+COUNTIF('All Completed Runs - HPR'!A4:A63,"W")+COUNTIF('All Completed Runs - HPR'!A4:A63,"X")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Y")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Z")

i.e. I have just tried to divide the first into the second. I expect to see 1 (100%) [21/21], but I am getting 41! It’s like the formula is adding the two together. I can’t understand what’s going wrong!

Please help!

Thanks in advance!

Olly.

 

[sleek12]

The issue with your formula is that the division operator / has a higher precedence than the addition operator +, which means that Excel is first performing the division, then adding the results of the COUNTIF functions. To get the desired result, you need to use parentheses to force Excel to perform the addition first, and then do the division.

Here's the corrected formula:

=(ROWS('All Completed Runs - HPR'!C4:C63)-COUNTBLANK('All Completed Runs - HPR'!C4:C63))/(COUNTIF('All Completed Runs - HPR'!A4:A63,"A")+COUNTIF('All Completed Runs - HPR'!A4:A63,"B")+COUNTIF('All Completed Runs - HPR'!A4:A63,"C")+COUNTIF('All Completed Runs - HPR'!A4:A63,"D")+COUNTIF('All Completed Runs - HPR'!A4:A63,"E")+COUNTIF('All Completed Runs - HPR'!A4:A63,"F")+COUNTIF('All Completed Runs - HPR'!A4:A63,"G")+COUNTIF('All Completed Runs - HPR'!A4:A63,"H")+COUNTIF('All Completed Runs - HPR'!A4:A63,"I")+COUNTIF('All Completed Runs - HPR'!A4:A63,"J")+COUNTIF('All Completed Runs - HPR'!A4:A63,"K")+COUNTIF('All Completed Runs - HPR'!A4:A63,"L")+COUNTIF('All Completed Runs - HPR'!A4:A63,"M")+COUNTIF('All Completed Runs - HPR'!A4:A63,"N")+COUNTIF('All Completed Runs - HPR'!A4:A63,"O")+COUNTIF('All Completed Runs - HPR'!A4:A63,"P")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Q")+COUNTIF('All Completed Runs - HPR'!A4:A63,"R")+COUNTIF('All Completed Runs - HPR'!A4:A63,"S")+COUNTIF('All Completed Runs - HPR'!A4:A63,"T")+COUNTIF('All Completed Runs - HPR'!A4:A63,"U")+COUNTIF('All Completed Runs - HPR'!A4:A63,"V")+COUNTIF('All Completed Runs - HPR'!A4:A63,"W")+COUNTIF('All Completed Runs - HPR'!A4:A63,"X")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Y")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Z"))

Note the parentheses around the two COUNTIF functions, which group them together and ensure that they are added before the division is performed.

 

[ollyhughes1982]

The issue with your formula is that the division operator / has a higher precedence than the addition operator +, which means that Excel is first performing the division, then adding the results of the COUNTIF functions. To get the desired result, you need to use parentheses to force Excel to perform the addition first, and then do the division.

Here's the corrected formula:

=(ROWS('All Completed Runs - HPR'!C4:C63)-COUNTBLANK('All Completed Runs - HPR'!C4:C63))/(COUNTIF('All Completed Runs - HPR'!A4:A63,"A")+COUNTIF('All Completed Runs - HPR'!A4:A63,"B")+COUNTIF('All Completed Runs - HPR'!A4:A63,"C")+COUNTIF('All Completed Runs - HPR'!A4:A63,"D")+COUNTIF('All Completed Runs - HPR'!A4:A63,"E")+COUNTIF('All Completed Runs - HPR'!A4:A63,"F")+COUNTIF('All Completed Runs - HPR'!A4:A63,"G")+COUNTIF('All Completed Runs - HPR'!A4:A63,"H")+COUNTIF('All Completed Runs - HPR'!A4:A63,"I")+COUNTIF('All Completed Runs - HPR'!A4:A63,"J")+COUNTIF('All Completed Runs - HPR'!A4:A63,"K")+COUNTIF('All Completed Runs - HPR'!A4:A63,"L")+COUNTIF('All Completed Runs - HPR'!A4:A63,"M")+COUNTIF('All Completed Runs - HPR'!A4:A63,"N")+COUNTIF('All Completed Runs - HPR'!A4:A63,"O")+COUNTIF('All Completed Runs - HPR'!A4:A63,"P")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Q")+COUNTIF('All Completed Runs - HPR'!A4:A63,"R")+COUNTIF('All Completed Runs - HPR'!A4:A63,"S")+COUNTIF('All Completed Runs - HPR'!A4:A63,"T")+COUNTIF('All Completed Runs - HPR'!A4:A63,"U")+COUNTIF('All Completed Runs - HPR'!A4:A63,"V")+COUNTIF('All Completed Runs - HPR'!A4:A63,"W")+COUNTIF('All Completed Runs - HPR'!A4:A63,"X")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Y")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Z"))

Note the parentheses around the two COUNTIF functions, which group them together and ensure that they are added before the division is performed.

Ah, brilliant. Thanks so much!

 

[Fluff]

It should be

=(ROWS('All Completed Runs - HPR'!C4:C63)-COUNTBLANK('All Completed Runs - HPR'!C4:C63))/(COUNTIF('All Completed Runs - HPR'!A4:A63,"A")+COUNTIF('All Completed Runs - HPR'!A4:A63,"B")+COUNTIF('All Completed Runs - HPR'!A4:A63,"C")+COUNTIF('All Completed Runs - HPR'!A4:A63,"D")+COUNTIF('All Completed Runs - HPR'!A4:A63,"E")+COUNTIF('All Completed Runs - HPR'!A4:A63,"F")+COUNTIF('All Completed Runs - HPR'!A4:A63,"G")+COUNTIF('All Completed Runs - HPR'!A4:A63,"H")+COUNTIF('All Completed Runs - HPR'!A4:A63,"I")+COUNTIF('All Completed Runs - HPR'!A4:A63,"J")+COUNTIF('All Completed Runs - HPR'!A4:A63,"K")+COUNTIF('All Completed Runs - HPR'!A4:A63,"L")+COUNTIF('All Completed Runs - HPR'!A4:A63,"M")+COUNTIF('All Completed Runs - HPR'!A4:A63,"N")+COUNTIF('All Completed Runs - HPR'!A4:A63,"O")+COUNTIF('All Completed Runs - HPR'!A4:A63,"P")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Q")+COUNTIF('All Completed Runs - HPR'!A4:A63,"R")+COUNTIF('All Completed Runs - HPR'!A4:A63,"S")+COUNTIF('All Completed Runs - HPR'!A4:A63,"T")+COUNTIF('All Completed Runs - HPR'!A4:A63,"U")+COUNTIF('All Completed Runs - HPR'!A4:A63,"V")+COUNTIF('All Completed Runs - HPR'!A4:A63,"W")+COUNTIF('All Completed Runs - HPR'!A4:A63,"X")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Y")+COUNTIF('All Completed Runs - HPR'!A4:A63,"Z"))

 

[Fluff]

Or significantly shorter

=(ROWS('All Completed Runs - HPR'!C4:C63)-COUNTBLANK('All Completed Runs - HPR'!C4:C63))/SUM(COUNTIF('All Completed Runs - HPR'!A4:A63,CHAR(SEQUENCE(26,,65))))