totalizer with missing on daily basic

Copper Contributor
Hello, I am looking for a formula that would help us calculate our totalizers (time and flow in liters) when we do not have the information on a daily basis.

thumbnail_20240207_110109.jpg

3 Replies

@lechatchante 

 

Would you be able to explain what you mean? It sounds (given the words you used in that brief, one sentence description) like you're asking how to calculate totals when you don't know what the numbers are to be used in making the calculation. Which, of course, doesn't make sense. 

 

In addition to a better description of what you're trying to do, please post not an image, but the actual file. Put it on OneDrive or GoogleDrive with a link pasted here that grants access to it.

I think I have an idea of what you're looking to do but I'm not entirely certain. If you're able to create a sample workbook (Even if it's only 7 days worth of data) that would be excellent. Please indicate what the expected results would be even if you have to do it manually.

@lechatchante  I have 2 thoughts on what you might want and have created a sample sheet (attached).

option 1: NET where the value is only shown when a data point exists and gives a result based on prior valid point.  Formula sample:

=IF(C2="","",MIN(C2:C$50)-MAX(C$1:C1))

option 2: EST (estimate) where the values inbetween are estimated based on the #days in between. sample formula:

=IF(ROW()=ROW($E$1)+1,C2,LET(prior,XMATCH(MAX(IF(ISNUMBER(C$1:C1),$A$1:$A1,0)),$A$1:$A1),next,XMATCH(MIN(IF(ISNUMBER(C2:C$50),$A2:$A$50,9E+99)),$A$1:$A$50),(INDEX(C$1:C$50, next)-INDEX(C$1:C$50, prior))/(next-prior)))

 

m_tarler_0-1707331028881.png