Accumulated volumes to daily volume with missing data

[cardsfan13]

Hi, I need help with tracking accumulated volumes (or values) on a daily basis. I have values that come in (typically every week day) that I want to semi-automate with excel to track daily volumes. My issue comes from missing data over the weekends. I want to transform the accumulated volume to a daily value and want to create a formula that I can use going forward to reduce manual work.

The difficulty is that I want to smooth out the data using an average when I have missing values. For example you can see the screen shot shows a period of 2 days without volume. That would mean I'd want to take the value for 1/11 and subtract the value for 1/8 and then average that value across the 3 days to have a daily figure that makes sense. Does anyone know of a way to do this? I tried with IF statements and couldn't figure it out. Ideally I would be able to simply enter accumulated volumes on a daily basis and have the daily value populate for me.

 

[EXCEL MAX]

You can put this formula in first row of "Daily Volume" column and fill down cells.
Dont forget to replace "A1" in formula with first cell address in your column "Accumulated Volume".
=IFERROR(IF(A1="","",SUMIF($A$1:$A1,">0")/COUNTIF($A$1:$A1,">0")),"")

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want.

22 04 27.xlsm
	A	B	C
1	Date	Accumulated	Daily
2	8/01/2022	1834543
3	9/01/2022		361.3333333
4	10/01/2022		361.3333333
5	11/01/2022	1835627	361.3333333
6	12/01/2022	1835971	344
7	13/01/2022	1836342	371
8	14/01/2022	1836689	347
9	15/01/2022	1837097	408
10	16/01/2022		326
11	17/01/2022		326
12	18/01/2022	1838075	326
Daily Volume

Cell Formulas
Range	Formula
C3:C12	C3	=LET(nr,INDEX(FILTER(ROW(B3:B$20),B3:B$20>0),1),pr,MAX(FILTER(ROW(B$2:B2),B$2:B2>0)),(INDEX(B:B,nr)-INDEX(B:B,pr))/(nr-pr))

 

[cardsfan13]

Welcome to the MrExcel board!

See if this does what you want.

22 04 27.xlsm
	A	B	C
1	Date	Accumulated	Daily
2	8/01/2022	1834543
3	9/01/2022		361.3333333
4	10/01/2022		361.3333333
5	11/01/2022	1835627	361.3333333
6	12/01/2022	1835971	344
7	13/01/2022	1836342	371
8	14/01/2022	1836689	347
9	15/01/2022	1837097	408
10	16/01/2022		326
11	17/01/2022		326
12	18/01/2022	1838075	326
Daily Volume

Cell Formulas
Range	Formula
C3:C12	C3	=LET(nr,INDEX(FILTER(ROW(B3:B$20),B3:B$20>0),1),pr,MAX(FILTER(ROW(B$2:B2),B$2:B2>0)),(INDEX(B:B,nr)-INDEX(B:B,pr))/(nr-pr))

Hi Peter - this is awesome! I am actually trying to do this in Google Sheets, which doesn't have the LET function... any idea how to work around that? I am trying to figure it out without the LET function, but it is super complicated. Appreciate the help!!

 

[Joe4]

As you have discovered, while Excel and Google Sheets have many similarities, they are NOT the same. So solutions for one may not work in the other.
For this reason, when posting questions about Google Sheets, please do the following:

1. Post them in the appropriate forum. They should NOT be posted to the "Excel Questions" forum, but rather the "General Discussion & Other Applications" forum (note the description of that forum):

2. Be sure to clearly state in the title and/or original question that this is, in fact, a "Google Sheets" question, so people who are helping you know what you are using, and can answer appropriately.

I have moved this thread to the appropriate forum for you.

 

[Peter_SSs]

.. doesn't have the LET function... any idea how to work around that?

If it is only the LET function that you don't have then this is the same formula with LET removed.

22 04 27.xlsm
	A	B	C
1	Date	Accumulated	Daily
2	8/01/2022	1834543
3	9/01/2022		361.3333333
4	10/01/2022		361.3333333
5	11/01/2022	1835627	361.3333333
6	12/01/2022	1835971	344
7	13/01/2022	1836342	371
8	14/01/2022	1836689	347
9	15/01/2022	1837097	408
10	16/01/2022		326
11	17/01/2022		326
12	18/01/2022	1838075	326
Daily Volume

Cell Formulas
Range	Formula
C3:C12	C3	=(INDEX(B:B,INDEX(FILTER(ROW(B3:B$20),B3:B$20>0),1))-INDEX(B:B,MAX(FILTER(ROW(B$2:B2),B$2:B2>0))))/(INDEX(FILTER(ROW(B3:B$20),B3:B$20>0),1)-MAX(FILTER(ROW(B$2:B2),B$2:B2>0)))

 

[cardsfan13]

If it is only the LET function that you don't have then this is the same formula with LET removed.

22 04 27.xlsm
	A	B	C
1	Date	Accumulated	Daily
2	8/01/2022	1834543
3	9/01/2022		361.3333333
4	10/01/2022		361.3333333
5	11/01/2022	1835627	361.3333333
6	12/01/2022	1835971	344
7	13/01/2022	1836342	371
8	14/01/2022	1836689	347
9	15/01/2022	1837097	408
10	16/01/2022		326
11	17/01/2022		326
12	18/01/2022	1838075	326
Daily Volume

Cell Formulas
Range	Formula
C3:C12	C3	=(INDEX(B:B,INDEX(FILTER(ROW(B3:B$20),B3:B$20>0),1))-INDEX(B:B,MAX(FILTER(ROW(B$2:B2),B$2:B2>0))))/(INDEX(FILTER(ROW(B3:B$20),B3:B$20>0),1)-MAX(FILTER(ROW(B$2:B2),B$2:B2>0)))

Thank you Peter for your help! This is a life saver.

 

[Peter_SSs]

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