How to calculate based on consecutive blanks?

M

MrMeerkat

New Member
Joined
Sep 8, 2023
Messages
3
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
Hello everyone, this request sounds easy but i can’t seem to wrap my head around doing it. Thanks for you help in advance.

IMG_4432.jpeg

Help needed:

Left Table - Input
Right Table - Desired Output

In the desired output:

Streak - based on the consecutive blanks starting from first blank in the respective product column to last blank based on latest date input in the Date column
Last Price - Last filled value
2nd Last price - Second last filled value
3rd Last Price - Third last filled value
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
I'm certain there'll be a more elegant solution than this, but this might do until a better one comes along...
Book1
ABCDEFGHIJKLM
1DateABCDProductStreakLast Price2nd last3rd last
21-Aug4A61274
32-AugB96  
43-AugC0197
54-AugD263 
65-Aug
76-Aug7
87-Aug6
98-Aug
109-Aug3
1110-Aug127
1211-Aug
1312-Aug9
1413-Aug
1514-Aug6
1615-Aug
1716-Aug1
18
Sheet2
Cell Formulas
RangeFormula
J2J2=COUNTBLANK(INDEX(B2:B17,MATCH(1E+100,B2:B17)):B17)
K2K2=LOOKUP(2,1/(B2:B17<>""),B2:B17)
L2L2=IFERROR(INDEX(B2:B17,LARGE(IF(B2:B17<>"",ROW(B2:B17)-ROW(B2)+1),2)),"")
M2M2=IFERROR(INDEX(B2:B17,LARGE(IF(B2:B17<>"",ROW(B2:B17)-ROW(B2)+1),3)),"")
J3J3=COUNTBLANK(INDEX(C2:C17,MATCH(1E+100,C2:C17)):C17)
K3K3=LOOKUP(2,1/(C2:C17<>""),C2:C17)
L3L3=IFERROR(INDEX(C2:C17,LARGE(IF(C2:C17<>"",ROW(C2:C17)-ROW(C2)+1),2)),"")
M3M3=IFERROR(INDEX(C2:C17,LARGE(IF(C2:C17<>"",ROW(C2:C17)-ROW(C2)+1),3)),"")
J4J4=COUNTBLANK(INDEX(D2:D17,MATCH(1E+100,D2:D17)):D17)
K4K4=LOOKUP(2,1/(D2:D17<>""),D2:D17)
L4L4=IFERROR(INDEX(D2:D17,LARGE(IF(D2:D17<>"",ROW(D2:D17)-ROW(D2)+1),2)),"")
M4M4=IFERROR(INDEX(D2:D17,LARGE(IF(D2:D17<>"",ROW(D2:D17)-ROW(D2)+1),3)),"")
J5J5=COUNTBLANK(INDEX(E2:E17,MATCH(1E+100,E2:E17)):E17)
K5K5=LOOKUP(2,1/(E2:E17<>""),E2:E17)
L5L5=IFERROR(INDEX(E2:E17,LARGE(IF(E2:E17<>"",ROW(E2:E17)-ROW(E2)+1),2)),"")
M5M5=IFERROR(INDEX(E2:E17,LARGE(IF(E2:E17<>"",ROW(E2:E17)-ROW(E2)+1),3)),"")
 
Upvote 0
kevin9999 said:
I'm certain there'll be a more elegant solution than this, but this might do until a better one comes along...
Book1
ABCDEFGHIJKLM
1DateABCDProductStreakLast Price2nd last3rd last
21-Aug4A61274
32-AugB96  
43-AugC0197
54-AugD263 
65-Aug
76-Aug7
87-Aug6
98-Aug
109-Aug3
1110-Aug127
1211-Aug
1312-Aug9
1413-Aug
1514-Aug6
1615-Aug
1716-Aug1
18
Sheet2
Cell Formulas
RangeFormula
J2J2=COUNTBLANK(INDEX(B2:B17,MATCH(1E+100,B2:B17)):B17)
K2K2=LOOKUP(2,1/(B2:B17<>""),B2:B17)
L2L2=IFERROR(INDEX(B2:B17,LARGE(IF(B2:B17<>"",ROW(B2:B17)-ROW(B2)+1),2)),"")
M2M2=IFERROR(INDEX(B2:B17,LARGE(IF(B2:B17<>"",ROW(B2:B17)-ROW(B2)+1),3)),"")
J3J3=COUNTBLANK(INDEX(C2:C17,MATCH(1E+100,C2:C17)):C17)
K3K3=LOOKUP(2,1/(C2:C17<>""),C2:C17)
L3L3=IFERROR(INDEX(C2:C17,LARGE(IF(C2:C17<>"",ROW(C2:C17)-ROW(C2)+1),2)),"")
M3M3=IFERROR(INDEX(C2:C17,LARGE(IF(C2:C17<>"",ROW(C2:C17)-ROW(C2)+1),3)),"")
J4J4=COUNTBLANK(INDEX(D2:D17,MATCH(1E+100,D2:D17)):D17)
K4K4=LOOKUP(2,1/(D2:D17<>""),D2:D17)
L4L4=IFERROR(INDEX(D2:D17,LARGE(IF(D2:D17<>"",ROW(D2:D17)-ROW(D2)+1),2)),"")
M4M4=IFERROR(INDEX(D2:D17,LARGE(IF(D2:D17<>"",ROW(D2:D17)-ROW(D2)+1),3)),"")
J5J5=COUNTBLANK(INDEX(E2:E17,MATCH(1E+100,E2:E17)):E17)
K5K5=LOOKUP(2,1/(E2:E17<>""),E2:E17)
L5L5=IFERROR(INDEX(E2:E17,LARGE(IF(E2:E17<>"",ROW(E2:E17)-ROW(E2)+1),2)),"")
M5M5=IFERROR(INDEX(E2:E17,LARGE(IF(E2:E17<>"",ROW(E2:E17)-ROW(E2)+1),3)),"")
Click to expand...
Thank you for the reply!

I have 1 more small request.. which is to add one more column in the output to indicate the highest number of consecutive blanks in each product.
kevin9999 said:
I'm certain there'll be a more elegant solution than this, but this might do until a better one comes along...
Book1
ABCDEFGHIJKLM
1DateABCDProductStreakLast Price2nd last3rd last
21-Aug4A61274
32-AugB96  
43-AugC0197
54-AugD263 
65-Aug
76-Aug7
87-Aug6
98-Aug
109-Aug3
1110-Aug127
1211-Aug
1312-Aug9
1413-Aug
1514-Aug6
1615-Aug
1716-Aug1
18
Sheet2
Cell Formulas
RangeFormula
J2J2=COUNTBLANK(INDEX(B2:B17,MATCH(1E+100,B2:B17)):B17)
K2K2=LOOKUP(2,1/(B2:B17<>""),B2:B17)
L2L2=IFERROR(INDEX(B2:B17,LARGE(IF(B2:B17<>"",ROW(B2:B17)-ROW(B2)+1),2)),"")
M2M2=IFERROR(INDEX(B2:B17,LARGE(IF(B2:B17<>"",ROW(B2:B17)-ROW(B2)+1),3)),"")
J3J3=COUNTBLANK(INDEX(C2:C17,MATCH(1E+100,C2:C17)):C17)
K3K3=LOOKUP(2,1/(C2:C17<>""),C2:C17)
L3L3=IFERROR(INDEX(C2:C17,LARGE(IF(C2:C17<>"",ROW(C2:C17)-ROW(C2)+1),2)),"")
M3M3=IFERROR(INDEX(C2:C17,LARGE(IF(C2:C17<>"",ROW(C2:C17)-ROW(C2)+1),3)),"")
J4J4=COUNTBLANK(INDEX(D2:D17,MATCH(1E+100,D2:D17)):D17)
K4K4=LOOKUP(2,1/(D2:D17<>""),D2:D17)
L4L4=IFERROR(INDEX(D2:D17,LARGE(IF(D2:D17<>"",ROW(D2:D17)-ROW(D2)+1),2)),"")
M4M4=IFERROR(INDEX(D2:D17,LARGE(IF(D2:D17<>"",ROW(D2:D17)-ROW(D2)+1),3)),"")
J5J5=COUNTBLANK(INDEX(E2:E17,MATCH(1E+100,E2:E17)):E17)
K5K5=LOOKUP(2,1/(E2:E17<>""),E2:E17)
L5L5=IFERROR(INDEX(E2:E17,LARGE(IF(E2:E17<>"",ROW(E2:E17)-ROW(E2)+1),2)),"")
M5M5=IFERROR(INDEX(E2:E17,LARGE(IF(E2:E17<>"",ROW(E2:E17)-ROW(E2)+1),3)),"")
Click to expand...
thank you for the reply!
I do have 1 more request:

I would like to add on 1 more column in the output indicating the highest number of consecutive blanks in each product.

So my output would look like this:
Product|Highest Consec
A | 6
B | 9
C | 9
D | 8
 
Upvote 0
Book1
ABCDEFGHIJKLMN
1DateABCDProductHighest ConsecStreakLast Price2nd last3rd last
21-Aug4A661274
32-AugB996  
43-AugC90197
54-AugD8263 
65-Aug
76-Aug7
87-Aug6
98-Aug
109-Aug3
1110-Aug127
1211-Aug
1312-Aug9
1413-Aug
1514-Aug6
1615-Aug
1716-Aug1
18
Sheet2
Cell Formulas
RangeFormula
J2J2=MAX(FREQUENCY((B2:B17="")*ROW(B2:B17),(B2:B17<>"")*ROW(B2:B17)))
K2K2=COUNTBLANK(INDEX(B2:B17,MATCH(1E+100,B2:B17)):B17)
L2L2=LOOKUP(2,1/(B2:B17<>""),B2:B17)
M2M2=IFERROR(INDEX(B2:B17,LARGE(IF(B2:B17<>"",ROW(B2:B17)-ROW(B2)+1),2)),"")
N2N2=IFERROR(INDEX(B2:B17,LARGE(IF(B2:B17<>"",ROW(B2:B17)-ROW(B2)+1),3)),"")
J3J3=MAX(FREQUENCY((C2:C17="")*ROW(C2:C17),(C2:C17<>"")*ROW(C2:C17)))
K3K3=COUNTBLANK(INDEX(C2:C17,MATCH(1E+100,C2:C17)):C17)
L3L3=LOOKUP(2,1/(C2:C17<>""),C2:C17)
M3M3=IFERROR(INDEX(C2:C17,LARGE(IF(C2:C17<>"",ROW(C2:C17)-ROW(C2)+1),2)),"")
N3N3=IFERROR(INDEX(C2:C17,LARGE(IF(C2:C17<>"",ROW(C2:C17)-ROW(C2)+1),3)),"")
J4J4=MAX(FREQUENCY((D2:D17="")*ROW(D2:D17),(D2:D17<>"")*ROW(D2:D17)))
K4K4=COUNTBLANK(INDEX(D2:D17,MATCH(1E+100,D2:D17)):D17)
L4L4=LOOKUP(2,1/(D2:D17<>""),D2:D17)
M4M4=IFERROR(INDEX(D2:D17,LARGE(IF(D2:D17<>"",ROW(D2:D17)-ROW(D2)+1),2)),"")
N4N4=IFERROR(INDEX(D2:D17,LARGE(IF(D2:D17<>"",ROW(D2:D17)-ROW(D2)+1),3)),"")
J5J5=MAX(FREQUENCY((E2:E17="")*ROW(E2:E17),(E2:E17<>"")*ROW(E2:E17)))
K5K5=COUNTBLANK(INDEX(E2:E17,MATCH(1E+100,E2:E17)):E17)
L5L5=LOOKUP(2,1/(E2:E17<>""),E2:E17)
M5M5=IFERROR(INDEX(E2:E17,LARGE(IF(E2:E17<>"",ROW(E2:E17)-ROW(E2)+1),2)),"")
N5N5=IFERROR(INDEX(E2:E17,LARGE(IF(E2:E17<>"",ROW(E2:E17)-ROW(E2)+1),3)),"")
 
Upvote 0
Here are some alternatives you might consider, for your 365 version at least. My formulas tend to be longer but can be just copied down instead of individually manually pointing the formulas at the correct columns.

I have also altered the sample data slightly in case you might have an example like column B. If so, I wasn't sure what results you would want for Streak and High Consec.

23 09 08.xlsm
ABCDEHIJKLMN
1DateABCDProductHigh ConsecStreakLast Price2nd last3rd last
21-Aug4A661274
32-AugB1616 
43-AugC90197
54-AugD8263
65-Aug
76-Aug7
87-Aug
98-Aug
109-Aug3
1110-Aug127
1211-Aug
1312-Aug9
1413-Aug
1514-Aug6
1615-Aug
1716-Aug1
MrMeerkat
Cell Formulas
RangeFormula
J2:J5J2=LET(d,FILTER(B$2:E$17,B$1:E$1=I2),r,ROW(A$2:A$17),MAX(FREQUENCY((d="")*r,(d<>"")*r)))
K2:K5K2=A$17-IFNA(LOOKUP(9^9,FILTER(B$2:E$17,B$1:E$1=I2),A$2:A$17),A1-1)
L2:N2,L5:M5,L4:N4,L3L2=LET(t,SORTBY(FILTER(B$2:E$17,B$1:E$1=I2),A$2:A$17,-1),f,FILTER(t,t<>"",NA()),IFNA(TAKE(TRANSPOSE(f),,3),""))
Dynamic array formulas.
 
Upvote 0
In J2 copy down.
Excel Formula: 
=AGGREGATE(14,6,ROW($A$2:$A$17),1)-AGGREGATE(14,6,(ROW($A$2:$A$17))/(INDEX($B$2:$E$17,,MATCH($I2,$B$1:$E$1,0))<>""),1)
In K2 copy to full range K2:N5
Excel Formula: 
=IFERROR(INDEX(INDEX($B:$E,,MATCH($I2,$B$1:$E$1,0)),AGGREGATE(14,6,(ROW($A$2:$A$17))/(INDEX($B$2:$E$17,,MATCH($I2,$B$1:$E$1,0))<>""),COLUMNS($K2:K2))),"")
 
Upvote 0
Peter_SSs said:
Here are some alternatives you might consider, for your 365 version at least. My formulas tend to be longer but can be just copied down instead of individually manually pointing the formulas at the correct columns.

I have also altered the sample data slightly in case you might have an example like column B. If so, I wasn't sure what results you would want for Streak and High Consec.

23 09 08.xlsm
ABCDEHIJKLMN
1DateABCDProductHigh ConsecStreakLast Price2nd last3rd last
21-Aug4A661274
32-AugB1616 
43-AugC90197
54-AugD8263
65-Aug
76-Aug7
87-Aug
98-Aug
109-Aug3
1110-Aug127
1211-Aug
1312-Aug9
1413-Aug
1514-Aug6
1615-Aug
1716-Aug1
MrMeerkat
Cell Formulas
RangeFormula
J2:J5J2=LET(d,FILTER(B$2:E$17,B$1:E$1=I2),r,ROW(A$2:A$17),MAX(FREQUENCY((d="")*r,(d<>"")*r)))
K2:K5K2=A$17-IFNA(LOOKUP(9^9,FILTER(B$2:E$17,B$1:E$1=I2),A$2:A$17),A1-1)
L2:N2,L5:M5,L4:N4,L3L2=LET(t,SORTBY(FILTER(B$2:E$17,B$1:E$1=I2),A$2:A$17,-1),f,FILTER(t,t<>"",NA()),IFNA(TAKE(TRANSPOSE(f),,3),""))
Dynamic array formulas.
Click to expand...
Thanks to everyone for the input.

Sorry if i did not make myself clear, but for the highest consecutive and streak, right now the formula is calculating until Row 17.

What I desire is for this formula to consider the whole column from B to E, but only do the calculation based on whether column A itself is filled with the dates or not. This way i do not have to edit the formula every time I input an additional date, while it still being updated automatically.
 
Upvote 0
Would 1000 rows be sufficient? If not, increase that figure in the formulas.

23 09 08.xlsm
ABCDEHIJKLMN
1DateABCDProductHigh ConsecStreakLast Price2nd last3rd last
21-Aug4A661274
32-AugB1616 
43-AugC90197
54-AugD8263
65-Aug
76-Aug7
87-Aug
98-Aug
109-Aug3
1110-Aug127
1211-Aug
1312-Aug9
1413-Aug
1514-Aug6
1615-Aug
1716-Aug1
18
MrMeerkat (3)
Cell Formulas
RangeFormula
J2:J5J2=LET(d,FILTER(FILTER(B$2:E$1000,B$1:E$1=I2),A$2:A$1000<>""),r,SEQUENCE(ROWS(d)),MAX(FREQUENCY(IF(d="",r),IF(d<>"",r))))
K2:K5K2=MAX(A$2:A$1000)-IFNA(LOOKUP(9^9,FILTER(B$2:E$1000,B$1:E$1=I2),A$2:A$1000),A$2-1)
L2:N2,L5:M5,L4:N4,L3L2=LET(t,SORTBY(FILTER(B$2:E$1000,B$1:E$1=I2),A$2:A$1000,-1),f,FILTER(t,t<>"",NA()),IFNA(TAKE(TRANSPOSE(f),,3),""))
Dynamic array formulas.
 
Last edited:
Upvote 0
Change the formula in J2 as
Excel Formula: 
=COUNTA($A$1:$A$10000)-AGGREGATE(14,6,(ROW($A$2:$A$17))/(INDEX($B$2:$E$17,,MATCH($I2,$B$1:$E$1,0))<>""),1)
 
Upvote 0
kvsrinivasamurthy said:
Change the formula in J2 as
Click to expand...
You would need to change all the $17 to $10000, though if you were looking for a formula that worked in earlier versions, simpler would be the following (which also allows for empty columns)
Excel Formula: 
=LOOKUP(9^9,A:A)-IFNA(LOOKUP(9^9,INDEX(B:E,0,MATCH(I2,B$1:E$1,0)),A:A),A$2-1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
405
mikefisher
M
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
912
Legacy 352679
L
N
  • Question Question
VBA - Difficult issue to solve for - Any help/direction GREATLY appreciated!
Replies
1
Views
636
MCLIFTO8
MCLIFTO8
B
  • Question Question
VBA/Macro (dynamic ranges) IF And Statement that uses interior color in column A and blank in column F
Replies
0
Views
686
blooeyez
B
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
287
cokeoverpepsi123
C

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top