# How to tell excel in formula to calculate the difference in cells between the first previous non-blank cell and the next previous non-blank cell

#### TroubledFormula

##### New Member
Dear All,

As a last resort I hope someone can help me out.

I require excel to calculate the difference in cells when a value a few rows below changes. The difference in amount of cells between the previous non-blank cell and next non-blank cell is a number I'll be using in another formula.
I've attached a small simple example. I know with index match function how to find the next non-blank cell but not how to find the previous non blank cell in a range.

Every tip is much appreciated and I thank you for your time.

#### Attachments

• Difference between non-blank cells.png
5.1 KB · Views: 7

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### jasonb75

##### Well-known Member
This is how I would do it, I expect there are other ways.
Book1 (version 1).xlsb
ABCD
1Result
21102.5
32205
43307.5
5440008020
6512040
76103.333333
8770006020
Sheet2
Cell Formulas
RangeFormula
D2:D8D2=C2/(INDEX(FREQUENCY(IF(\$B\$2:\$B\$8="",\$A\$2:\$A\$8),IF(\$B\$2:\$B\$8<>"",\$A\$2:\$A\$8)),COUNTIF(B\$1:B1,"<>")+1)+1)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

#### TroubledFormula

##### New Member
This is how I would do it, I expect there are other ways.
Book1 (version 1).xlsb
ABCD
1Result
21102.5
32205
43307.5
5440008020
6512040
76103.333333
8770006020
Sheet2
Cell Formulas
RangeFormula
D2:D8D2=C2/(INDEX(FREQUENCY(IF(\$B\$2:\$B\$8="",\$A\$2:\$A\$8),IF(\$B\$2:\$B\$8<>"",\$A\$2:\$A\$8)),COUNTIF(B\$1:B1,"<>")+1)+1)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thank you for your reply but if expand the range in your formula (Ive got 3000+ rows) than the result is a different value.

#### jasonb75

##### Well-known Member
It will still work if your data is consistent with the example.
I would need to see the expanded formula and an example where it is not giving the correct result in order to identify why it is not working as expected.

#### Peter_SSs

##### MrExcel MVP, Moderator

I expect there are other ways.

@TroubledFormula
Welcome to the MrExcel board!

One of those other ways would be ..
(I assume that column B actually has a heading in row 1 so) insert a new row 2 and enter 0 in cells A2 & B2. You could then hide row 2 if you want.

20 05 19.xlsm
ABCD
1TextResult
200
31102.5
42205
53307.5
6440008020
7512040
86103.33333
9770006020
108
119
1210
Count rows
Cell Formulas
RangeFormula
D3:D12D3=IF(C3="","",C3/(MATCH(TRUE,INDEX(B3:B\$5000<>"",0),0)-1+A3-LOOKUP(9^99,B\$2:B2,A\$2:A2)))

#### TroubledFormula

##### New Member
Thank you both for the solutions! I've managed to get both work in the file I have!

What a great service!

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

#### shaowu459

##### Well-known Member
Book11.xlsx
ABCD
1ValueResult
21102.5
32205
43307.5
5440008020
6512040
76103.333333
8770006020
986020
1096020
111050006020
Sheet6
Cell Formulas
RangeFormula
D2:D11D2=C2/(MIN(IF(B2:B9999<>"",ROW(2:9999)))-MAX((B\$1:B1<>"")*ROW(\$1:1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Replies
5
Views
285
Replies
10
Views
325
Replies
3
Views
85
Replies
23
Views
323
Replies
3
Views
137