Array Function - cell to remain blank

  • Thread starter Thread starter mare
  • Start date Start date
M

mare

I am a novice on Excel, however, I don't get easy projects.

I have a spreadsheet where D6:D15 have values. In cell D17
is the Total, which is an average of what exists in D6:D15.
However, some of these cells have zero value, so this was
taken into consideration when building the formula for D17.

This is the formula: {=AVERAGE(IF(D6:D15<>0,D6:D15,""))}

Right now, there are no values in D6:D15 (nothing to input
yet), but in cell D17 is the message: #DIV/0!.

I've tried to see if I can fix the above array formula,
but I'm making it worse.

How do I get D17 (Total) to be empty when there is nothing
(yet) in D6:D15?

Any suggestions will be helpful. Thank you.
 
Assuming that you can't have negative values

=IF(SUM(D6:D15)=0,0,AVERAGE(IF(D6:D15<>0,D6:D15,"")))

if you can have negative values

=IF(COUNTBLANK(D6:D15)=10,0,AVERAGE(IF(D6:D15<>0,D6:D15,"")))
 
To Peo Sjoblom:

I tried your first formula (I cannot have negative values).

It works, until I test it by inputting some values into it:

Cell Value
D6 4
D7 3
D8 2
D9 2
D10 2
D11 2
D12 2
D13 2
D14 (BLANK)
D15 (BLANK)

In the Total cell (D17), it reads #VALUE!.

How to I get D17 to give me the average?

P.S., the values in the cells are formatted at "Number".
 
It is impossible to get a value error using your numbers,

=IF(SUM(D6:D15)=0,0,AVERAGE(IF(D6:D15<>0,D6:D15,"")))

using the above and your numbers and entered with ctrl + shift & enter I get
2.375 which is correct
Since average ignores text and this particular formula would ignore zero if
there were any I can only assume
that you applied it incorrectly. I can email you a sample using your number
plus 2 blank cells which will
show it works.
 
Peo, I would appreciate it very much if you could email me
a sample with my names, plus 2 blank cells, because I
can't figure it out what I'm doing wrong. Thank you.
 
Back
Top