Insert average value into missing value cells

[ChildInTime]

Hello!

So I have column which has random missing numeric data. Is there a simple way to automatically insert the average value of all non missing values into those cells with missing values? The column contains a lot of data, so I don't want to do it all manually, how else can I do it?

Thank you!

[Sam Capricci]

Well after you calculate the average you could do a find and replace finding all blank cells and replacing with the "average" you calculated.

[ben_hensel]

I think what you want is something like,

A1:A100
a range of numbers with scattered empty cells.

B1:100
range of numbers with falsified data points

B1 = IF(A1<>"", A1, AVERAGE($A$1:$A$100))
pull down

Now B1:B100 has the output range you need.

Does that do what you want?

[ChildInTime]

Well after you calculate the average you could do a find and replace finding all blank cells and replacing with the "average" you calculated.

Wow, didn't thought about that but it's very simple idea and should really work!

I think what you want is something like,

A1:A100
a range of numbers with scattered empty cells.

B1:100
range of numbers with falsified data points

B1 = IF(A1<>"", A1, AVERAGE($A$1:$A$100))
pull down

Now B1:B100 has the output range you need.

Does that do what you want?

Your formula makes sense, but how do I insert all values automatically? I mean imagine this is my column, just much larger: http://i.imgur.com/5E943Ce.png. I need to automatically insert average of existing values into missing ones. That said Sambo Kid's advice is simple and should do the trick, but since I have several columns I need to define "missing value" for each of them so all of them won't change with Replace All command.

[Sam Capricci]

Just do your find and replace highlighting only the column/columns you want.

EDIT: Oh, and thanks for the rep points.

[ChildInTime]

Just do your find and replace highlighting only the column/columns you want.

EDIT: Oh, and thanks for the rep points.

worked perfectly. Sometimes you just need to sit and think, because it's so simple haha

[Sam Capricci]

Great, and don't forget to mark the post as solved if that did it for you.
(top of the post under thread tools)

[ChildInTime]

done, thanks!