Formula to Calculate Difference in Value After Finding First Non Blank Cell

Copper Contributor

Hello all,

 

I need help with a formula and the subsequent calculation. Please see the attached workbook. This is a sample work book illustrating my dilemma. 

 

Column B is the date of purchase

Column C is the vendor

Column D is the item

Column F returns a "1" each time a vendor has a new order date, and begins fresh for each new Company.

Column G returns a "1" only if the "Item 5" was purchased by the company. Item 5 is our company's best product offering.

Now, Column E returns the calculation of days since today's date and each date they purchased "Item 5"

 

What I desire is a new column that produces only the days since their most recent purchase of "Item 5", which for Company a would be 600. However as we move down the column, I would like it to find and calculate the number of days (or the difference) between the each time they purchased "Item 5".

So for Company A, the results would be: 600, 108

For Company B, the results would be: 928

For Company C, the results would be: 937, 70

For Company D, the results would be 250, 174, 238

I would like it to produce blank cells in between each values.

 

Thank you ahead of time!

My ultimate goal is to extrapolate data that calculates the average reorder rate for our vendors, and then checking to see if this number is decreasing (they are ordering more often) as time goes on.

9 Replies

@BrittD2 

Assuming that you have Excel in Office 2019 or Microsoft 365, enter the following array formula in H2, comfirmed with Ctrl+Shift+Enter:

 

=IF(G2=1,TEXTJOIN(", ",TRUE,IF($C$2:$C2=$C2,$E$2:$E2,"")),"")

 

Fill down.

Hans, thank you. I should've been more clear. I only want one single value per cell.
So H13 is 600, and H25 is 708.
H51 is 250, H53 is 174 and H58 is 238.

@BrittD2 

In H2:

 

=IF(G2=1,E2-MAXIFS($E$1:$E1,$C$1:$C1,$C2),"")

 

Fill down.

Thats great, thank you for your assistance!

@Hans Vogelaar 

 

You helped me so beautifully last time, I wondered if you might be able to please help again. 

 

On this thread, you gave the formula for Column H to calculate the running difference in the values. 

 

Now I would like a new Column "I" and a formula to return only the second value found for each grouping of rows, and otherwise return blank, essentially returning the second smallest number in each sequence and starting over for each new company.

 

So I25 would return 108

I43 would return 70

I53 would return 174

 

Thank you ahead of time!

@BrittD2 

Does this do what you want? In I2:

 

=IF(AND(G2=1,COUNTIFS(C$2:C2,C2,H$2:H2,">0")=2),H2,"")

 

Fill down.

@Hans Vogelaar

It did. I'm very grateful for your help, thank you!