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]

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.

 

[jasonb75]

This is how I would do it, I expect there are other ways.

Book1 (version 1).xlsb
	A	B	C	D
1				Result
2	1		10	2.5
3	2		20	5
4	3		30	7.5
5	4	4000	80	20
6	5		120	40
7	6		10	3.333333
8	7	7000	60	20
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=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]

This is how I would do it, I expect there are other ways.

Book1 (version 1).xlsb
	A	B	C	D
1				Result
2	1		10	2.5
3	2		20	5
4	3		30	7.5
5	4	4000	80	20
6	5		120	40
7	6		10	3.333333
8	7	7000	60	20
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=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]

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]

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
	A	B	C	D
1		Text		Result
2	0	0
3	1		10	2.5
4	2		20	5
5	3		30	7.5
6	4	4000	80	20
7	5		120	40
8	6		10	3.33333
9	7	7000	60	20
10	8
11	9
12	10
Count rows

Cell Formulas
Range	Formula
D3:D12	D3	=IF(C3="","",C3/(MATCH(TRUE,INDEX(B3:B$5000<>"",0),0)-1+A3-LOOKUP(9^99,B$2:B2,A$2:A2)))

 

[TroubledFormula]

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

What a great service!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[shaowu459]

another solution for your reference

Book11.xlsx
	A	B	C	D
1		Value		Result
2	1		10	2.5
3	2		20	5
4	3		30	7.5
5	4	4000	80	20
6	5		120	40
7	6		10	3.333333
8	7	7000	60	20
9	8		60	20
10	9		60	20
11	10	5000	60	20
Sheet6

Cell Formulas
Range	Formula
D2:D11	D2	=C2/(MIN(IF(B2:B9999<>"",ROW(2:9999)))-MAX((B$1:B1<>"")*ROW($1:1)))
Press CTRL+SHIFT+ENTER to enter array formulas.