Ask Your Question

How to fill empty cells with average? [closed]

asked 2017-05-10 07:50:55 +0100

LearningLibre gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-03 21:08:42.460898


Bad idea! Don't mix up raw data and evaluation if you want to get reliable results.

Lupp gravatar imageLupp ( 2017-05-10 11:49:16 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-05-10 07:56:12 +0100

updated 2017-05-10 07:58:20 +0100

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)

edit flag offensive delete link more


Hmm. I wrote =aggregate(1,0,Q1:Q153) when Q7 was activated but the result was Error:501.

LearningLibre gravatar imageLearningLibre ( 2017-05-10 08:11:07 +0100 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-10 08:17:33 +0100 )edit

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?

Lupp gravatar imageLupp ( 2017-05-10 11:59:53 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2017-05-10 12:14:21 +0100 )edit

Quoting @Mike Kaganski: "...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.

( @LearningLibre may have chosen his user name consciously.)

Lupp gravatar imageLupp ( 2017-05-10 12:32:48 +0100 )edit

Question Tools

1 follower


Asked: 2017-05-10 07:50:55 +0100

Seen: 409 times

Last updated: May 10 '17