(Google Sheet Here, The Sheet in Question: Results)

In order to get this 64%, I have used this formula: =LEFT(D6,2)/RIGHT(D6,2) and this continues in each cell, the D6 value changing accordingly. I have made conditional formatting so that where the cell contains #DIV/0!, it changes text colour to the same as the background, as you can see if you highlight the cells.

However, I am trying to find an average out of the percentages, but it comes up with the #DIV/0! error as it is averaging all cells, not just the number ones. How could I get it to create an average for just the percentages in each column.

CodePudding user response：

instead of:

=LEFT(D6,2)/RIGHT(D6,2)

use:

=IFERROR(QUERY(QUERY(, "select "&D6), "offset 1", ))

and to get average you can do:

=INDEX(AVERAGE(IFERROR(INDEX(SPLIT(D6:D, "/"),,1)/
                       INDEX(SPLIT(D6:D, "/"),,2))))