Array Function - cell to remain blank

[mare]

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

I have a spreadsheet where D615 have values. In cell D17
is the Total, which is an average of what exists in D615.
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(D615<>0,D615,""))}

Right now, there are no values in D615 (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 D615?

Any suggestions will be helpful. Thank you.

 

[Peo Sjoblom]

Assuming that you can't have negative values

=IF(SUM(D615)=0,0,AVERAGE(IF(D615<>0,D615,"")))

if you can have negative values

=IF(COUNTBLANK(D615)=10,0,AVERAGE(IF(D615<>0,D615,"")))

 

[mare]

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".

 

[Peo Sjoblom]

It is impossible to get a value error using your numbers,

=IF(SUM(D615)=0,0,AVERAGE(IF(D615<>0,D615,"")))

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.

 

[mare]

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.

 

[Peo Sjoblom]

Sure, once I get your email address