How to fill blank cells with formulas?

[Aqil]

Hi,

I need to automate some data entry since I have a huge database in the following form:

The A column contains the sample number. Each row has a sample so from A1 and downwards its just

1
2
3
...
k

The B column contains the number of observations in each sample . Importantly, some samples has no observations at all, so n=0 in some cases.

The C column contains the average value or estimate for each sample. When there are no observations in the sample, the corresponding estimate in the C column is blank.

I have reason to believe that when n=0, the best estimate for this sample is the average of the estimate in the row above and the row below. So I did a formula in the D column: =IF(B3=0;AVERAGE(C2;C4);0) and copied down to the end. The I took =SUM(C3:D3) in the E column and copied down. This would work just fine if it wasn't for the fact that it quite often are several samples without observations in a row. So I need a more general method. For example:

A B C D E
1 1 10 "" 10
2 0 "" 8 8
3 1 6 0 6
4 0 "" 6 6
5 0 "" 2 2
6 1 2 0 2
7 0 "" 2 2
8 0 "" #DIV/0! #DIV/0!
9 0 "" 1 1
10 1 1 0 1

The result above is insufficient. The result I'm looking for is rather


A B C D E
1 1 10 "" 10
2 0 "" 8 8
3 1 6 0 6
4 0 "" 4 4
5 0 "" 4 4
6 1 2 0 2
7 0 "" 1.5 1.5
8 0 "" 1.5 1.5
9 0 "" 1.5 1.5
10 1 1 0 1

So both E4 and E5 should be the average of E3 and E6 while E7, E8 and E9 should each be the average of E6 and E10. More than 10 blanks is very rare so the proposed method doesn't have to be applicable to larger gaps than that. Please tell me if I'm being unclear, and thanks for helping!

Sincerely
Aqil

 

[Marcelo Branco]

Hi Aqil,

Maybe this

<TABLE style="WIDTH: 320pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=427><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Sample#</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 67pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=89>Observations</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=103>AVG(observed)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=107>Avg (estimated)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>AVG</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1,5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1,5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1,5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1,5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1,5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1,5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>10


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2708778 class=xl64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1</TD></TR></TBODY></TABLE>

Formula in D2 (Array-formula)
=IF(B2=0,AVERAGE(INDEX($C$2:$C$11,IF(B2=0,MATCH(ROW(B2),IF($B$2:$B$11<>0,ROW($B$2:$B$11))))):INDEX($C$2:$C$11,IF(B2=0,MATCH(10^9-ROW(B2),IF($B$2:$B$11=0,10^9,10^9-ROW($B$2:$B$11)),-1)+1))),"")
Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)
copy down

Formula in E2
=C2+D2
copy down


HTH

M.

 

[Marcelo Branco]

A little bit smaller formula
=IF(B2=0,AVERAGE(INDEX($C$2:$C$11,MATCH(ROW(B2),IF($B$2:$B$11<>0,ROW($B$2:$B$11)))):INDEX($C$2:$C$11,MATCH(10^9-ROW(B2),IF($B$2:$B$11=0,10^9,10^9-ROW($B$2:$B$11)),-1)+1)),"")
Ctrl+Shift+Enter

M.

 

[cgcamal]

Hi Aqil,

Assuming the first and last cell in column C contains a number you can try with:

Sub Average()
Wv = WorksheetFunction.CountIf(Range("B1:B" & Range("B1").End(xlDown).Row), ">0")
For i = 1 To (Range("B1").End(xlDown).Row)
If i < (Range("B1").End(xlDown).Row) Then
    If Cells(i, 2) > 0 And Cells(i + 1, 2) = 0 Then
        Ul = Cells(i, 2).Row
        Ll = Range("C:C").Find(What:="*", After:=Range("C" & Ul), LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Row
 
        For j = Ul + 1 To Ll - 1
            Cells(i, 4) = Cells(i, 3)
            Cells(j, 4) = WorksheetFunction.average(Cells(Ul, 3), Cells(Ll, 3))
        Next
    ElseIf Cells(i, 2) > 0 And Cells(i - 1, 2) = 0 Then
        Cells(i, 4) = Cells(i, 3)
    End If
Else
 Cells(i, 4) = Cells(i, 3)
End If
Next
End Sub

Regards

 

[Aqil]

cgcamal,

I've never done any VBA or any kind of programming for that matter, which I think the code you gave me is. So I'm afraid I don't know how to apply it. I'm willing to learn though, if somebody can point me to some introduction which is just about enough to complete this task.

Marcelo Branco,

After a couple of frustrating hours, I got a slightly modified version of your formula to work properly:

=IF(B2:B11=0;AVERAGE(INDEX($C$2:$C$11;IF(B2:B11=0;MATCH(ROW(B2:B11);IF($B$2:$B$11<>0;ROW($B$2:$B$11))))):INDEX($C$2:
$C$11;IF(B2:B11=0;MATCH(10^9-ROW(B2:B11);IF($B$2:$B$11=0;10^9;10^9-ROW($B$2:$B$11));-1)+1)));"")

I just replaced , with ; (I think this is because I have a European version)
And instead of B2 I typed B2:B11 .

It seems to be working fine, at least in the trial I did, so Thank You very much!

Now, I'm obviously curious about how you did it . I don't understand how it works and the index part of the formula is the big mistery, so I would appreciate if somebody could tell me step by step what it means. I get the basics of the commands used here but I don't get how it is applied. How come the =IF formula [value_if_false] is another =IF formula which in turn has no specified [value_if_false]? How does =INDEX():INDEX() work?

Thank you again!

 

[cgcamal]

cgcamal,

I've never done any VBA or any kind of programming for that matter, which I think the code you gave me is. So I'm afraid I don't know how to apply it. I'm willing to learn though, if somebody can point me to some introduction which is just about enough to complete this task.

Hi Aqil,

Even when It looks you've got the desired result with the nice formula given by Marcelo, in order learn how to apply Visual Basic code try this steps:

1-) Open a new workbook;

2-) Copy the sample data you show in this post to have this input data;

Excel Workbook
	A	B	C	D
1	1	1	10
2	2	0
3	3	1	6
4	4	0
5	5	0
6	6	1	2
7	7	0
8	8	0
9	9	0
10	10	1	1
Sheet1

3-) Having that, press Alt+F11. This action will be open Visual Basic Editor in MS-Office applications.

4-) Select the workbook where you want to insert the VBA macro;

5-) Within VBA editor, go to "Insert" menu and select "Module" (normally the VBA code is inserted in modules);

6-) Copy the code I've posted in this thread and paste it within the module.
and you'll see something like this:

7-) Go to Excel Worksheet and press Alt+F8 to open the list of macros availables.

8 -) Select the macro "Average" that is the name I gave to my macro, (see the first line of the code and you'll
see "Sub Average", then, the part after Sub is the name of the macro.)

9-) Once you've selected the macro you want, click "Run" and you'll see the result in worksheet.

Excel Workbook
	A	B	C	D
1	1	1	10	10
2	2	0		8
3	3	1	6	6
4	4	0		4
5	5	0		4
6	6	1	2	2
7	7	0		1.5
8	8	0		1.5
9	9	0		1.5
10	10	1	1	1
Sheet1

Hope this helps.

Best regards.

 

[Marcelo Branco]

cgcamal,

I've never done any VBA or any kind of programming for that matter, which I think the code you gave me is. So I'm afraid I don't know how to apply it. I'm willing to learn though, if somebody can point me to some introduction which is just about enough to complete this task.

Marcelo Branco,

After a couple of frustrating hours, I got a slightly modified version of your formula to work properly:

=IF(B2:B11=0;AVERAGE(INDEX($C$2:$C$11;IF(B2:B11=0;MATCH(ROW(B2:B11);IF($B$2:$B$11<>0;ROW($B$2:$B$11))))):INDEX($C$2:
$C$11;IF(B2:B11=0;MATCH(10^9-ROW(B2:B11);IF($B$2:$B$11=0;10^9;10^9-ROW($B$2:$B$11));-1)+1)));"")

I just replaced , with ; (I think this is because I have a European version)
And instead of B2 I typed B2:B11 .

It seems to be working fine, at least in the trial I did, so Thank You very much!

Now, I'm obviously curious about how you did it . I don't understand how it works and the index part of the formula is the big mistery, so I would appreciate if somebody could tell me step by step what it means. I get the basics of the commands used here but I don't get how it is applied. How come the =IF formula [value_if_false] is another =IF formula which in turn has no specified [value_if_false]? How does =INDEX():INDEX() work?

Thank you again!

Hi Aqil,

I'm happy that its working.

The INDEX function normally returns a value, but returns a Reference when used INDEX():INDEX() or something like A2:INDEX(), INDEX():A3 etc.

So all the formula does is to find both next values with observations - above and below the cells that had 0(zero) observations and applies the AVERAGE function using these values.

Curiously my Excel-version (Portuguese) also uses ; (semi-colon) as separators. I had some work to change it to , (comma) trying to do things easier to you .

Tks for the feedback

M.

 

[Marcelo Branco]

Aqil,

Complementing my previous post.

I think you should not have changed B2 to B2:B11. It worked perfect for me with B2 only and copied down.

But if it worked to you with B2:B11...great!

M.

 

[Aqil]

Sorry for the late response. I have nothing important to add so... thanks again to both of you!

And Marcelo, you're right, B2 only and copy down works fine and is better. I realized this when I tried B2:B37601 and my computer jammed.