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!

 

[Eric W]

Perhaps:

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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"][/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]9/1/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"]2/1/2017[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]6/1/2017[/TD]
[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]9/1/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>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, 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,MONTH($C$3:$E$3)))-MAX(IF($C$3:$E$3<C10,MONTH($C$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]



That's an array formula, confirm with Control+Shift+Enter.

It seems to work on the sample data, I suspect that it might have issues on your larger sheet, but give it a try and let me know.

 

[FranzV]

You could also put the formula in one column, but I left it in separate ones for easier undestanding.

[B]Excel 2016 (Windows) 64 bit[/B][TABLE]
<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD]AuditDate[/TD]
[TD]Value[/TD]
[TD="align: right"][/TD]
[TD]Date[/TD]
[TD]Row[/TD]
[TD]Days[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]28-12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01-01[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]03-01[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]02-01[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]05-01[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]03-01[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]09-01[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"]04-01[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]02-02[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]05-01[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]06-01[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]07-01[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]08-01[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]09-01[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10-01[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11-01[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Hoja1[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD]=IFERROR([COLOR=#0000FF]MATCH([COLOR=#FF0000][Date]-1,tAudits[AuditDate],1[/COLOR])+1,""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD]=COUNTIF([COLOR=#0000FF][Row],[@Row][/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD]=IFERROR([COLOR=#0000FF]INDEX([COLOR=#FF0000]tAudits[Value],[Row][/COLOR])/[Days],""[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Domyzon]

Thank you for the help Eric!

I got the sumproduct part of the formula working, but I am having difficulties with MIN-IF and MAX-IF.

Could you please elaborate on:

1. MIN(IF($C$3:$E$3>=C10,MONTH($C$3:$E$3))

This gives me values of 1 for each.

2. MAX(<font color="Red" style="background-color: rgb(250, 250, 250);">IF(<font color="Green">$C$3:$E$3<c10,month($C$3:$E$3</c10,month())

Is this formula missing anything? Separation or other criteria?

 

[Domyzon]

Thank you Franz!

Could you please write the formulas as A1, B3, etc? I am having difficulties understanding which ones are the correct values for the names.

Also, 10.01.2017 and 11.01.2017 show 50. But they should be less since the values are divided by all the days up to 2.02.2017. Would they change if the available dates are dragged until 2.02.2017?

 

[Eric W]

My apologies! You'd think I'd learn at some point. The forum software here sometimes interprets a < as part of an HTML tag. So when < appears in a formula, you need to put spaces around it or part of the formula will get truncated, which is what happened in post #2. Here's the correct formula:

=SUMPRODUCT(--(C10>$B$3:$D$3),--(C10<=$C$3:$E$3),$C$4:$E$4)/(MIN(IF($C$3:$E$3>=C10,MONTH($C$3:$E$3)))-MAX(IF($C$3:$E$3< C10,MONTH($C$3:$E$3))))

confirmed with Control+Shift+enter. Put in C11 and drag to the right. See if that works better for you.

 

[Domyzon]

Ah, I have had this issue as well, now I know it's the HTML!

I still can't get the second part to work:

MIN(IF($C$3:$E$3>=C10,MONTH($C$3:$E$3)))
MAX(IF($C$3:$E$3< C10,MONTH($C$3:$E$3)))
<c10,month($c$3:$e$3)))

Should they also include a IF false argument?</c10,month($c$3:$e$3)))

 

[FranzV]

Thank you Franz!

Could you please write the formulas as A1, B3, etc? I am having difficulties understanding which ones are the correct values for the names.

Also, 10.01.2017 and 11.01.2017 show 50. But they should be less since the values are divided by all the days up to 2.02.2017. Would they change if the available dates are dragged until 2.02.2017?

Sorry about the mistake in the formula for 10/02 and 11/02, I decided to change the formulas for a simpler version in the last moment and did not realize it stopped working for that scenario. Bellow you can find the other version of the formulas in A1 notation (though I always recommend using tables because it is much easier to write and debug formulas).

*Audit table must be ordered by date from oldest to most recent (ascending).

[B]Excel 2016 (Windows) 64 bit[/B][TABLE]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #5B9BD5"]AuditDate[/TD]
[TD="bgcolor: #5B9BD5"]Value[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #5B9BD5"]Date[/TD]
[TD="bgcolor: #5B9BD5"]Start[/TD]
[TD="bgcolor: #5B9BD5"]End[/TD]
[TD="bgcolor: #5B9BD5"]AuditValue[/TD]
[TD="bgcolor: #5B9BD5"]Days[/TD]
[TD="bgcolor: #5B9BD5"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #DDEBF7, align: right"]28/12/2016[/TD]
[TD="bgcolor: #DDEBF7, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]01/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]01/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]03/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 12 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 3 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 4 [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]02/01/2017[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"] 12 [/TD]
[TD="align: right"] 3 [/TD]
[TD="align: right"] 4 [/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: #DDEBF7, align: right"]05/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]03/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]01/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]03/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 12 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 3 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 4 [/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]09/01/2017[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"]04/01/2017[/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]05/01/2017[/TD]
[TD="align: right"] 10 [/TD]
[TD="align: right"] 3 [/TD]
[TD="align: right"] 3 [/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="bgcolor: #DDEBF7, align: right"]02/02/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]05/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]03/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]05/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 10 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 3 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 3 [/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]06/01/2017[/TD]
[TD="align: right"]05/01/2017[/TD]
[TD="align: right"]09/01/2017[/TD]
[TD="align: right"] 24 [/TD]
[TD="align: right"] 5 [/TD]
[TD="align: right"] 5 [/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]07/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]05/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]09/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 24 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 5 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 5 [/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]05/01/2017[/TD]
[TD="align: right"]09/01/2017[/TD]
[TD="align: right"] 24 [/TD]
[TD="align: right"] 5 [/TD]
[TD="align: right"] 5 [/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]09/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]05/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]09/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 24 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 5 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 5 [/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/01/2017[/TD]
[TD="align: right"]09/01/2017[/TD]
[TD="align: right"]31/01/2017[/TD]
[TD] [/TD]
[TD="align: right"] 23 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]11/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]09/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]31/01/2017[/TD]
[TD="bgcolor: #DDEBF7"] [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 23 [/TD]
[TD="bgcolor: #DDEBF7"] [/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/02/2017[/TD]
[TD="align: right"]01/02/2017[/TD]
[TD="align: right"]02/02/2017[/TD]
[TD="align: right"] 100 [/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"] 50 [/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]02/02/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]01/02/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]02/02/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 100 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 2 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 50 [/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]03/02/2017[/TD]
[TD="align: right"]02/02/2017[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Hoja1[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD]=IFERROR([COLOR=#0000FF]MAX([COLOR=#FF0000]DATE([COLOR=#00FF00]YEAR([COLOR=#800080]Hoja1!$D2[/COLOR]),MONTH([COLOR=#800080]Hoja1!$D2[/COLOR]),1[/COLOR]),INDEX([COLOR=#00FF00]Hoja1!$A$2:$A$6,MATCH([COLOR=#800080]Hoja1!$D2-1,Hoja1!$A$2:$A$6,1[/COLOR])[/COLOR])[/COLOR]),""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD]=IFERROR([COLOR=#0000FF]MIN([COLOR=#FF0000]EDATE([COLOR=#00FF00]DATE([COLOR=#800080]YEAR([COLOR=#008080]Hoja1!$D2[/COLOR]),MONTH([COLOR=#008080]Hoja1!$D2[/COLOR]),1[/COLOR]),1[/COLOR])-1,INDEX([COLOR=#00FF00]Hoja1!$A$2:$A$6,MATCH([COLOR=#800080]Hoja1!$D2-1,Hoja1!$A$2:$A$6,1[/COLOR])+1[/COLOR])[/COLOR]),""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD]=IFERROR([COLOR=#0000FF]INDEX([COLOR=#FF0000] Hoja1!$B$2:$B$6, MATCH([COLOR=#00FF00] Hoja1!$F2, Hoja1!$A$2:$A$6, 0 [/COLOR]) [/COLOR]),""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD]=IFERROR([COLOR=#0000FF]Hoja1!$F2-Hoja1!$E2+1,""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD]=IFERROR([COLOR=#0000FF]Hoja1!$G2/Hoja1!$H2,""[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FranzV]

Or you can use array formulas that will work even if the tables are not sorted.


[B]Excel 2016 (Windows) 64 bit[/B][TABLE]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #5B9BD5"]AuditDate[/TD]
[TD="bgcolor: #5B9BD5"]Value[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #5B9BD5"]Date[/TD]
[TD="bgcolor: #5B9BD5"]Start[/TD]
[TD="bgcolor: #5B9BD5"]End[/TD]
[TD="bgcolor: #5B9BD5"]AuditValue[/TD]
[TD="bgcolor: #5B9BD5"]Days[/TD]
[TD="bgcolor: #5B9BD5"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #DDEBF7, align: right"]28/12/2016[/TD]
[TD="bgcolor: #DDEBF7, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]25-12-2016[/TD]
[TD="bgcolor: #DDEBF7, align: right"]01-12-2016[/TD]
[TD="bgcolor: #DDEBF7, align: right"]28-12-2016[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 6 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 28 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 0.2 [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30-12-2016[/TD]
[TD="align: right"]29-12-2016[/TD]
[TD="align: right"]31-12-2016[/TD]
[TD] [/TD]
[TD="align: right"] 3 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: #DDEBF7, align: right"]05/01/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]01-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]01-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]03-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 12 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 3 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 4.0 [/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]09/01/2017[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"]02-01-2017[/TD]
[TD="align: right"]01-01-2017[/TD]
[TD="align: right"]03-01-2017[/TD]
[TD="align: right"] 12 [/TD]
[TD="align: right"] 3 [/TD]
[TD="align: right"] 4.0 [/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="bgcolor: #DDEBF7, align: right"]02/02/2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]03-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]01-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]03-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 12 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 3 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 4.0 [/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]04-01-2017[/TD]
[TD="align: right"]04-01-2017[/TD]
[TD="align: right"]05-01-2017[/TD]
[TD="align: right"] 10 [/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"] 5.0 [/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]05-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]04-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]05-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 10 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 2 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 5.0 [/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]06-01-2017[/TD]
[TD="align: right"]06-01-2017[/TD]
[TD="align: right"]09-01-2017[/TD]
[TD="align: right"] 24 [/TD]
[TD="align: right"] 4 [/TD]
[TD="align: right"] 6.0 [/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]07-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]06-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]09-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 24 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 4 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 6.0 [/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]08-01-2017[/TD]
[TD="align: right"]06-01-2017[/TD]
[TD="align: right"]09-01-2017[/TD]
[TD="align: right"] 24 [/TD]
[TD="align: right"] 4 [/TD]
[TD="align: right"] 6.0 [/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]09-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]06-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]09-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 24 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 4 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 6.0 [/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10-01-2017[/TD]
[TD="align: right"]10-01-2017[/TD]
[TD="align: right"]31-01-2017[/TD]
[TD] [/TD]
[TD="align: right"] 22 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]11-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]10-01-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]31-01-2017[/TD]
[TD="bgcolor: #DDEBF7"] [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 22 [/TD]
[TD="bgcolor: #DDEBF7"] [/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]01-02-2017[/TD]
[TD="align: right"]01-02-2017[/TD]
[TD="align: right"]02-02-2017[/TD]
[TD="align: right"] 100 [/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"] 50.0 [/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]02-02-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]01-02-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]02-02-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"] 100 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 2 [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 50.0 [/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]03-02-2017[/TD]
[TD="align: right"]03-02-2017[/TD]
[TD="align: right"]28-02-2017[/TD]
[TD] [/TD]
[TD="align: right"] 26 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"]10-03-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]01-03-2017[/TD]
[TD="bgcolor: #DDEBF7, align: right"]31-03-2017[/TD]
[TD="bgcolor: #DDEBF7"] [/TD]
[TD="bgcolor: #DDEBF7, align: right"] 31 [/TD]
[TD="bgcolor: #DDEBF7"] [/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Hoja1[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD]=IFERROR([COLOR=#0000FF]INDEX([COLOR=#FF0000] $B$2:$B$6, MATCH([COLOR=#00FF00] $F2, $A$2:$A$6, 0 [/COLOR]) [/COLOR]),""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD]=IFERROR([COLOR=#0000FF]$F2-$E2+1,""[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD]=IFERROR([COLOR=#0000FF]$G2/$H2,""[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Array Formulas[/B][TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD]{=MAX([COLOR=#0000FF]IFERROR([COLOR=#FF0000]$A$2:$A$6*IF([COLOR=#00FF00]$A$2:$A$6<$D2,1,0[/COLOR]),0[/COLOR])+1,DATE([COLOR=#FF0000]YEAR([COLOR=#00FF00]$D2[/COLOR]),MONTH([COLOR=#00FF00]$D2[/COLOR]),1[/COLOR])[/COLOR])}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD]{=MIN([COLOR=#0000FF]IFERROR([COLOR=#FF0000]$A$2:$A$6*IF([COLOR=#00FF00]$A$2:$A$6>=$D2,1,1000000[/COLOR]),1000000[/COLOR]),EDATE([COLOR=#FF0000]DATE([COLOR=#00FF00]YEAR([COLOR=#800080]$D2[/COLOR]),MONTH([COLOR=#800080]$D2[/COLOR]),1[/COLOR]),1[/COLOR])-1[/COLOR])}[/TD]
[/TR]
</tbody>[/TABLE]
[B]Entered with Ctrl+Shift+Enter.[/B] If entered correctly, Excel will surround with curly braces {}. [B]Note: Do not try and enter the {} manually yourself[/B][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

What results are you getting? The MIN part should return a 3, and the MAX part should return a 0. We don't need a false argument for the IF, since if you leave it off, it defaults to FALSE, and the MIN and MAX functions will ignore FALSE values.

Are you familiar with array formulas and how to enter them?