Need help creating a formula that will ignore cells (within a range of cells)
that do not contain a value (calculated from a previous formula):
Used =IF(D12="n/a","",SUM(D12/C12)) to calculate the score % of a topic on
an audit form. If the topic does not apply, enter n/a and no value will be
calculated in the % cell. If score was 8 out of 10, value would be 80%.
However, when I want to calculate the overall score % for all the topics of
the audit, I need to be able to exclude the cells that do not contain a value
because they do not apply. Since the value of the cells change with every
audit (not every topic applies to every audit), I need to create a formula
that will automatically ignore the cells that do not contain values.
So in a range of cells E10:E19, if cells E11 and E15 do not contain a value,
I do not want my overall score % to use these cells when calculated
(SUM(E10:E19)/8), where 8 is automatically determined because I only have 8
cells with values. Or, 7 or 9, depending on the how many if any topics are
not applicable to the audit.

One way:

=AVERAGE(E10:E19)

Average will ignore text (including "").

In article ,
Michele wrote:

Need help creating a formula that will ignore cells (within a range of cells)
that do not contain a value (calculated from a previous formula):
Used =IF(D12="n/a","",SUM(D12/C12)) to calculate the score % of a topic on
an audit form. If the topic does not apply, enter n/a and no value will be
calculated in the % cell. If score was 8 out of 10, value would be 80%.
However, when I want to calculate the overall score % for all the topics of
the audit, I need to be able to exclude the cells that do not contain a value
because they do not apply. Since the value of the cells change with every
audit (not every topic applies to every audit), I need to create a formula
that will automatically ignore the cells that do not contain values.
So in a range of cells E10:E19, if cells E11 and E15 do not contain a value,
I do not want my overall score % to use these cells when calculated
(SUM(E10:E19)/8), where 8 is automatically determined because I only have 8
cells with values. Or, 7 or 9, depending on the how many if any topics are
not applicable to the audit.

Of course! Why didn't I think of that one?

Thanks!

"JE McGimpsey" wrote:

One way:

=AVERAGE(E10:E19)

Average will ignore text (including "").

In article ,
Michele wrote:

Need help creating a formula that will ignore cells (within a range of cells)
that do not contain a value (calculated from a previous formula):
Used =IF(D12="n/a","",SUM(D12/C12)) to calculate the score % of a topic on
an audit form. If the topic does not apply, enter n/a and no value will be
calculated in the % cell. If score was 8 out of 10, value would be 80%.
However, when I want to calculate the overall score % for all the topics of
the audit, I need to be able to exclude the cells that do not contain a value
because they do not apply. Since the value of the cells change with every
audit (not every topic applies to every audit), I need to create a formula
that will automatically ignore the cells that do not contain values.
So in a range of cells E10:E19, if cells E11 and E15 do not contain a value,
I do not want my overall score % to use these cells when calculated
(SUM(E10:E19)/8), where 8 is automatically determined because I only have 8
cells with values. Or, 7 or 9, depending on the how many if any topics are
not applicable to the audit.