Discussion Formula to Calculate Difference in Value After Finding First Non Blank Cell in Excel
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2139986#M89720
<P>Hello all,</P><P> </P><P>I need help with a formula and the subsequent calculation. Please see the attached workbook. This is a sample work book illustrating my dilemma. </P><P> </P><P>Column B is the date of purchase</P><P>Column C is the vendor</P><P>Column D is the item</P><P>Column F returns a "1" each time a vendor has a new order date, and begins fresh for each new Company.</P><P>Column G returns a "1" only if the "Item 5" was purchased by the company. Item 5 is our company's best product offering.</P><P>Now, Column E returns the calculation of days since today's date and each date they purchased "Item 5"</P><P> </P><P>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".</P><P>So for Company A, the results would be: 600, 108</P><P>For Company B, the results would be: 928</P><P>For Company C, the results would be: 937, 70</P><P>For Company D, the results would be 250, 174, 238</P><P>I would like it to produce blank cells in between each values.</P><P> </P><P>Thank you ahead of time!</P><P>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.</P>Tue, 16 Feb 2021 18:44:17 GMTBrittD22021-02-16T18:44:17ZFormula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2139986#M89720
<P>Hello all,</P><P> </P><P>I need help with a formula and the subsequent calculation. Please see the attached workbook. This is a sample work book illustrating my dilemma. </P><P> </P><P>Column B is the date of purchase</P><P>Column C is the vendor</P><P>Column D is the item</P><P>Column F returns a "1" each time a vendor has a new order date, and begins fresh for each new Company.</P><P>Column G returns a "1" only if the "Item 5" was purchased by the company. Item 5 is our company's best product offering.</P><P>Now, Column E returns the calculation of days since today's date and each date they purchased "Item 5"</P><P> </P><P>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".</P><P>So for Company A, the results would be: 600, 108</P><P>For Company B, the results would be: 928</P><P>For Company C, the results would be: 937, 70</P><P>For Company D, the results would be 250, 174, 238</P><P>I would like it to produce blank cells in between each values.</P><P> </P><P>Thank you ahead of time!</P><P>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.</P>Tue, 16 Feb 2021 18:44:17 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2139986#M89720BrittD22021-02-16T18:44:17ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140208#M89722
<P><LI-USER uid="968530"></LI-USER> </P>
<P>Assuming that you have Excel in Office 2019 or Microsoft 365, enter the following array formula in H2, comfirmed with Ctrl+Shift+Enter:</P>
<P> </P>
<P>=IF(G2=1,TEXTJOIN(", ",TRUE,IF($C$2:$C2=$C2,$E$2:$E2,"")),"")</P>
<P> </P>
<P>Fill down.</P>Tue, 16 Feb 2021 19:39:26 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140208#M89722Hans Vogelaar2021-02-16T19:39:26ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140258#M89724
Hans, thank you. I should've been more clear. I only want one single value per cell.<BR />So H13 is 600, and H25 is 708.<BR />H51 is 250, H53 is 174 and H58 is 238.Tue, 16 Feb 2021 19:54:20 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140258#M89724BrittD22021-02-16T19:54:20ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140274#M89727
<P><LI-USER uid="968530"></LI-USER> </P>
<P>Shouldn't H25 be 108?</P>Tue, 16 Feb 2021 20:03:14 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140274#M89727Hans Vogelaar2021-02-16T20:03:14ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140467#M89735
<P><LI-USER uid="127945"></LI-USER> </P><P> </P><P>Yes, of course, thank you!</P>Tue, 16 Feb 2021 20:57:39 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140467#M89735BrittD22021-02-16T20:57:39ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140591#M89738
<P><LI-USER uid="968530"></LI-USER> </P>
<P>In H2:</P>
<P> </P>
<P>=IF(G2=1,E2-MAXIFS($E$1:$E1,$C$1:$C1,$C2),"")</P>
<P> </P>
<P>Fill down.</P>Tue, 16 Feb 2021 23:03:39 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140591#M89738Hans Vogelaar2021-02-16T23:03:39ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140817#M89747
Thats great, thank you for your assistance!Tue, 16 Feb 2021 22:46:28 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2140817#M89747BrittD22021-02-16T22:46:28ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2235446#M94288
<P><LI-USER uid="127945"></LI-USER> </P><P> </P><P>You helped me so beautifully last time, I wondered if you might be able to please help again. </P><P> </P><P>On this thread, you gave the formula for Column H to calculate the running difference in the values. </P><P> </P><P>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.</P><P> </P><P>So I25 would return 108</P><P>I43 would return 70</P><P>I53 would return 174</P><P> </P><P>Thank you ahead of time!</P>Thu, 25 Mar 2021 15:54:56 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2235446#M94288BrittD22021-03-25T15:54:56ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2235499#M94291
<P><LI-USER uid="968530"></LI-USER> </P>
<P>Does this do what you want? In I2:</P>
<P> </P>
<P>=IF(AND(G2=1,COUNTIFS(C$2:C2,C2,H$2:H2,">0")=2),H2,"")</P>
<P> </P>
<P>Fill down.</P>Thu, 25 Mar 2021 16:10:39 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2235499#M94291Hans Vogelaar2021-03-25T16:10:39ZRe: Formula to Calculate Difference in Value After Finding First Non Blank Cell
https://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2235574#M94295
<LI-USER uid="127945" login="Hans Vogelaar"></LI-USER><BR /><BR />It did. I'm very grateful for your help, thank you!Thu, 25 Mar 2021 16:25:14 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-calculate-difference-in-value-after-finding-first-non/m-p/2235574#M94295BrittD22021-03-25T16:25:14Z