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
Joined
May 19, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
    Difference between non-blank cells.png
    5.1 KB · Views: 7

Some videos you may like

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
Joined
Dec 30, 2008
Messages
11,619
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 19, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
11,619
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 19, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
another solution for your reference
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,988
Messages
5,575,390
Members
412,659
Latest member
oliverreyes
Top