Calculate rates in periods

[candeniz]

Hi,
I have an excel table as in the image:

​

[TABLE="class: grid, width: 750, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]End Date[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Season[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]01.05.2019[/TD]
[TD="align: center"]15.05.2019[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]03.05.2019[/TD]
[TD="align: center"]OR[/TD]
[TD="align: center"]13.05.2019[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]16.05.2019[/TD]
[TD="align: center"]06.06.2019[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]End Date[/TD]
[TD="align: center"]10.05.2019[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20.05.2019[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]07.06.2019[/TD]
[TD="align: center"]03.07.2019[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]04.07.2019[/TD]
[TD="align: center"]31.07.2019[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"][/TD]
[TD="align: center"]110[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]01.08.2019[/TD]
[TD="align: center"]21.08.2019[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]22.08.2019[/TD]
[TD="align: center"]10.09.2019[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]11.09.2019[/TD]
[TD="align: center"]05.10.2019[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]06.10.2019[/TD]
[TD="align: center"]31.10.2019[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

So my questions are:

• How can I fix the formulas in F5 & H5 (to calculate the price between the selected dates even in one period or in a breaking period (H5; 3 days from season A and 4 days from season B)?
• What can be the formula in column D (D2:D9) to assign the same letter if price is same?

Thanks indeed for your precious helps.

 

[majidsiddique]

Hi Candeniz,
can you elaborate your calculation between dates?

 

[candeniz]

Hi Candeniz,
can you elaborate your calculation between dates?

Thanks for your reply.

The first formula in F5 will calculate the price regarding the criterias in F2 & F3 from the given rates in C2:C9. Here the crucial point is to make right calculation even in one period (as in F2 & F3 or in a broken period like H2 & H3). The second criterias (H2 & H3) are written as an example to display criterias varieties normaly I need one formula in F5 to calculate all cases.

The second formula I need in D3:D9 to assign the same letter regarding the figures in C2:C9. I mean same letter must be used again according to the previous figures similarities.

 

[AlanY]

could you confirm your example in F2 & F3, there are 8 days should equal to 80, similar to H2 & H3

 

[candeniz]

could you confirm your example in F2 & F3, there are 8 days should equal to 80, similar to H2 & H3

Sorry to state that the last day is always omitted from calculation so 10.05-03.05= 7 is right figure to calculate

 

[AlanY]

this works with a helper column E that can be hidden

Book1
	A	B	C	D	E	F	G	H	I
1	Start Date	End Date	Price	Season
2	01/05/2019	15/05/2019	10	A	10	Start Date	03/05/2019	13/05/2019	13/05/2019
3	16/05/2019	06/06/2019	20	B	10	End Date	10/05/2019	20/05/2019	20/07/2019
4	07/06/2019	03/07/2019	30	C	10
5	04/07/2019	31/07/2019	40	D	10	Price	70	110	1,920
6	01/08/2019	21/08/2019	50	E	10
7	22/08/2019	10/09/2019	40	D	-10
8	11/09/2019	05/10/2019	30	C	-10
9	06/10/2019	31/10/2019	10	A	-20
Sheet4

Cell Formulas
Range	Formula
G5	=SUMPRODUCT(--(G3>$A$2:$A$9),G3-$A$2:$A$9,$E$2:$E$9)-SUMPRODUCT(--(G2>$A$2:$A$9),G2-$A$2:$A$9,$E$2:$E$9)
E3	=C3-C2
D2	=CHOOSE(C2/10,"A","B","C","D","E")

 

[candeniz]

Thanks AlanY works fine but also have another approach from excelforum as in the following really works well:

Calculation Formula

{SUM(IF(($A$2:$A$9>F$3)+($B$2:$B$9<F$2),0,IF(1+$B$2:$B$9>F$3,F$3,1+$B$2:$B$9)-<f$2),0,if(1+$b$2:$b$9><f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
</f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
</f$2),0,if(1+$b$2:$b$9><f$2),0,if(1+$b$2:$b$9><f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
<f$2),0,if(1+$b$2:$b$9><f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
IF($A$2:$A$9<F$2,F$2,$A$2:$A$9))*($C$2:$C$9))}
Season Letters Formula

<code>=IF(C2="","",IFERROR(VLOOKUP($C2,$C$1:$D1,2,0),CHAR(64+SUMPRODUCT(($C$2:$C2<>"")/COUNTIFS($C$2:$C2,$C$2:$C2&"")))))

Regards</code></f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
</f$2),0,if(1+$b$2:$b$9></f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
</f$2),0,if(1+$b$2:$b$9>

 

[candeniz]

Re: Calculate rates in periods - SOLVED

My Previous message is incompleted now corrected in this link

<f$2),0,if(1+$b$2:$b$9><f$2,f$2,$a$2:$a$9))*($c$2:$c$9))<f$2),0,if(1+$b$2:$b$9><f$2,f$2<f$2,f$2,$a$2:$a$9))*($c$2:$c$9))<f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
<f$2),0,if(1+$b$2:$b$9><f$2,f$2,$a$2:$a$9))*($c$2:$c$9))<f$2),0,if(1+$b$2:$b$9><f$2,f$2,$a$2:$a$9))*($c$2:$c$9))
<f$2,f$2,$a$2:$a$9))
<code></code></f$2,f$2,$a$2:$a$9))
</f$2,f$2,$a$2:$a$9))*($c$2:$c$9))
</f$2,f$2,$a$2:$a$9))*($c$2:$c$9))<f$2),0,if(1+$b$2:$b$9></f$2),0,if(1+$b$2:$b$9></f$2,f$2<f$2,f$2,$a$2:$a$9))*($c$2:$c$9))<f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
</f$2,f$2,$a$2:$a$9))*($c$2:$c$9))<f$2),0,if(1+$b$2:$b$9></f$2),0,if(1+$b$2:$b$9>https://photos.app.goo.gl/GPRNnqsZqZuWR47r6<f$2),0,if(1+$b$2:$b$9><f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
</f$2,f$2,$a$2:$a$9))*($c$2:$c$9))}
</f$2),0,if(1+$b$2:$b$9>

 

[Fluff]

Re: Calculate rates in periods - SOLVED

Cross posted https://www.excelforum.com/excel-fo...7-calculate-rates-in-periods.html#post5067373

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.