Value and date range formula (Expert)

[Domyzon]

Hi

I am looking help for a formula concerning values and dates in a range.

Table 1 consist of aggregate audit values at specific dates. These audit values represent the sum of values between the dates.

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Audit values[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3.1.2017[/TD]
[TD]5.1.2017[/TD]
[TD]9.1.2017[/TD]
[/TR]
[TR]
[TD]Value[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]24[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 (Values to be filled by formula)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Audit values by date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1.01.2017[/TD]
[TD]2.01.2017[/TD]
[TD]3.01.2017[/TD]
[TD]4.01.2017[/TD]
[TD]5.01.2017[/TD]
[TD]6.01.2017[/TD]
[TD]7.01.2017[/TD]
[TD]8.01.2017[/TD]
[TD]9.01.2017[/TD]
[/TR]
[TR]
[TD]Value (formula needed)[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[/TR]
[TR]
[TD]Value filled by hand(EXAMPLE)[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
​
Conditions and thoughts:
1. Table 2 specific date has to find a date range to fit in Table 1.
2. Find the first audit date from Table 1 in a range that fits the specific date from Table 2.
Criteria for the first audit date:
- Has to be in the same month.
- If no first date/value in the month in audit data in the range for the specific date in Table 2, then assume 01. of the same month as first, and include it in the value formula. (As seen in the example, 01.01.2017 value is included in the 03.01.2017 audit value.)
- If there is first date/value in the month in audit data range, then exclude it and start the range from the next date.
****** id="cke_pastebin" style="position: absolute; top: 348px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD](formula)[/TD]
[/TR]
</tbody>[/TABLE]
</body>3. Find the last audit date from Table 1 in a range that fits the specific date from Table 2.
Criteria for the first audit date:
- Has to be in the same month, if not, then Table 2 values are filled until the last day of the given month.
- Always include the last date in a range.
4. Count range days - Use DateIf or Days formula?
5. Use the audit values and divide them equally among appropriate dates.

I hope my thoughts weren't too confusing, I just tried to provide a detailed description of what I wanted to do. Perhaps there's a simple combined formula out there that includes these criteria.

Thank you in advance!

 

[Domyzon]

I got it working now!

I swapped the MONTH formula to DAY. Does it work for you with MONTH?

Thank you both Eric and Franz, I can now combine both solutions!

 

[Eric W]

It does work for me with MONTH. If you get it to work with DAY, then I suspect we have different date formats. But it sounds like you're on the right track, so good job!

 

[Domyzon]

Thanks again!

 

[Eric W]

The dates in this example are MM/DD/YYYY, which probably don't match your sheet, but the formula should work.

	A	B	C	D	E	F	G	H	I	J	K
Table 1
Audit Values
Value
Table 2
Audit values by date
Value

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]1/9/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]1/4/2017[/TD]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]1/7/2017[/TD]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"]1/9/2017[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]{=SUMPRODUCT(--(C10>$B$3:$D$3),--(C10<=$C$3:$E$3),$C$4:$E$4)/(MIN(IF($C$3:$E$3>=C10,$C$3:$E$3))-MAX(IF($B$3:$E$3< C10,$B$3:$E$3)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



In B3 put the date before the start of the first period. Once that's in place, use the formula from C11 and drag right. It should properly account for changes in the months and years.