I have a set of data where some columns have empty cells. How can I set those empty cells the value that is the mean of the rest of the numbers in the same column?
Bad idea! Don’t mix up raw data and evaluation if you want to get reliable results.
Use AGGREGATE function, which ignores itself if it happens to be in its own range.
So, if you are working with, say, column C, and the range is C2:C10, and C4 is empty, then you may put there this:
=AGGREGATE(1, 0, C2:C10)
Hmm. I wrote =aggregate(1,0,Q1:Q153) when Q7 was activated but the result was Error:501.
This means “Invalid Character”. Make sure you use proper argument delimiters (I used “,” as this seems to be expected on this forum; my system, e.g., uses “;”).
I would hesitate to suggest the usage of functions like AGGREGATE and SUBTOTAL. They are outside, imo, of the range of reasonable spreadsheet usage. They are aliens anyway, not even accepting the ways functions are identified generally in spreadsheets and also when called via the api (‘FunctionAccess’).
May I suppose these functions were introduced just for “compatibility” once? Who knows?
I see your point. However, I see this as a matter of taste. The function has documented and well-defined behavior, that makes it fit in this case. The case itself, however, is somewhat … not good, but I’m in no position to tell user how to do their job.
Quoting @mikekaganski: “…but I’m in no position to tell user how to do their job.”
That’s an honorable statement and sounding smoothly modest.
Being an old teacher I cannot simply agree in every case. And this is more about “intermediary rows” than about the “alien” functions.
To get accepted in my teacheresque habits I am ready to gladly accept being teached in turn.
( @LearningLibre1 may have chosen his user name consciously.)