Is there an easy way to do this in Excel?

[thirtythree]

Say I have the following in a column:

23
45
__ (blank)
__
__
79
53

What I want to do is fill in all the blank rows with the average of the numbers above and below (45 and 79). The number of blank rows that need to be filled in varies.

 

[CubicZirconia]

I don't see why the average function wouldn't work.

Just select which cells need to be averaged by hand.

 

[thirtythree]

Originally posted by: CubicZirconia
I don't see why the average function wouldn't work.

Just select which cells need to be averaged by hand.

That works, but it's not terribly quick.

 

[petesamprs]

Here's a relatively crude formula that should do what you want.

Assuming your first data point starts in cell A7 and goes down from there, paste this in cell B7:

=IF(ISBLANK(A7),AVERAGE(IF(ISBLANK(A6),IF(ISBLANK(A5),IF(ISBLANK(A4),"X",A4),A5),A6),IF(ISBLANK(A8),IF(ISBLANK(A9),IF(ISBLANK(A10),"X",A10),A9),A8)),A7)

This assumes you have no more than three spaces between data points. If you do have more than 3, you can modify the average fomula to check more cells upward/downward to calculate the average

 

[thirtythree]

Hmm... the number of blank rows ranges from 1 to 30 or so. Plus I don't want all that in all the cells. Is there any way to use a formula then have it just leave the value behind?

 

[doan]

Excel has a interploate function

 

[thirtythree]

Originally posted by: doan
Excel has a interploate function

It does? I searched help but nothing came up.

 

[thirtythree]

.