| Test | Mark | Percent |
|---|---|---|
| B4 Test | 27/42 | 64% |
| B5 Test | #DIV/0! |
(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.
>Solution :
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))))