[Solved] Return an average, but not until data is entered

[rcrmonte3]

I have a column (d) that averages the numbers in another column (c) AVG=(c$1:c70). c70 is whatever cell number has a value ie c2, c3, c4, etc., so column d is the average of every value in column c. Is it possible to have cell d return a value of 0 if there is no value in column c, but still calculate the average when a value is entered in column c?
Every cell (from 1 to...) in column d has the AVG formula, which means the result of averaging column c fills all empty cells in column d after the actual result> Sounds confusing.
If cell c2=40 and c3=38, cell d3 will =39. All cells after d3 will =39 until a new value is entered in cell c4 and the formula in d4 will recalculate the average of c2:c4.
What I want to do is keep the formulas in each cell in column d, but if there is no value in c4, the formula in d will return 0 until a value is entered in c4, etc.
How to do that if it is possible?

Edit: Changed subject, was AVG=0 IF.....  Make your post understandable by others  -- MrProgrammer, forum moderator

[FJCC]

I am not sure I understand what you want. Do you need a formula like

Code: Select all

=IF(ISBLANK(C4);0;AVERAGE(C$1:C4))

in D4? That returns 0 if C4 is blank but returns the average of C1:C4 otherwise.

[Zizi64]

rcrmonte3,
Please upload your ODF type sample file here.

[rcrmonte3]

FJCC:
Your formula works very well, thank you.
However, where it is =IF(ISBLANK(c4) I need the c4 to increase by 1 in every column c cell when I do a 'fill down'. c4, c5, c6, etc

[rcrmonte3]

Zizi64:
What is an ODF type sample file? I am not an IT person so I have no clue of what you are asking.

[rcrmonte3]

FJCC:
Sorry, my mistake. I corrected a problem and then the formula workedthe way I need it to.

[RusselB]

If you put the formula FJCC gave you into D4, then copy it from D4 to another cell in column D, then the C4 in the ISBLANK will automatically update for the new row that you paste it into.
You can also paste into a range (ie: several rows) at once, and the formula will be correctly, and automatically adjusted for each row that it is pasted into.

An ODF sample file would be something like your actual spreadsheet. ODF stands for Open Document Format. In Calc the extension used for an ODF file is .ods (I like to think of it as Open Document Spreadsheet, though I don't know if that's officially correct)

[Zizi64]

What is an ODF type sample file?

ODF: Open Document Format. It is an International Standard.
The specific file extension is .ods for the Spreadsheet files. ODF is the native fileformat of the Apache OpenOffice and LibreOffice.

[rcrmonte3]

Thanks guys. Everything works OK now.