Calculating rates per month on average basis

[nikolacm]

Hi there,

I have daily rates of several hotel room categories which are refer to specific timeframes (i.e. 15/4/2022 to 19/5/2022). I am trying to calculate the rate per month. To this end I have used days formula sa the excel file shows. Is there any quicker way (which will minimize error) to calculate monthly rates ?

start period	15/4/2022	20/5/2022	3/6/2022	24/6/2022	22/7/2022	28/8/2022	18/9/2022	2/10/2022
end period	19/5/2022	2/6/2022	23/6/2022	21/7/2022	27/8/2022	17/9/2022	1/10/2022	15/10/2022
Standard Double - Mountain View (5 AMEA incl)	157​	173​	189​	206​	222​	206​	189​	173​
Standard Double - Sea View	173​	189​	206​	222​	238​	222​	206​	189​
Standard Double -SwimUP!	189​	206​	222​	238​	254​	238​	222​	206​
Family junior suite - Pool View	206​	222​	246​	262​	278​	262​	246​	222​
Family junior suite - Pool View & Swim UP	238​	254​	278​	294​	310​	294​	278​	254​
Family junior suite - Sea View	270​	286​	310​	326​	342​	326​	310​	286​
Family junior suite - Sea View & SwimUp !	302​	318​	342​	358​	374​	358​	342​	318​
Premium Suite Pool View	310​	326​	350​	366​	382​	366​	350​	326​
Premium Suite Sea View	342​	358​	382​	398​	414​	398​	382​	358​
Premium Suite Sea View & Swim Up	374​	390​	414​	430​	446​	430​	414​	390​
Helea Suite - Pool View	454​	470​	494​	510​	526​	510​	494​	470​
Helea Suite - Sea View	494​	510​	534​	550​	566​	550​	534​	510​

	April	May	June	July	August	September	October
Standard Double - Mountain View (5 AMEA incl)	157​	163.66​	192.17​	210.68​	219.46​	198.57​	173​
Standard Double - Sea View	173​	179.68​	208.18​	226.70​	235.48​	214.59​	189​
Standard Double -SwimUP!	189​	195.70​	224.20​	242.71​	251.50​	230.61​	206​
Family junior suite - Pool View	206​	211.71​	247.69​	266.74​	275.52​	254.63​	222​
Family junior suite - Pool View & Swim UP	238​	243.75​	279.72​	298.77​	307.56​	286.67​	254​
Family junior suite - Sea View	270​	275.78​	311.76​	330.81​	339.59​	318.70​	286​
Family junior suite - Sea View & SwimUp !	302​	307.81​	343.79​	362.84​	371.62​	350.73​	318​
Premium Suite Pool View	310​	315.82​	351.80​	370.85​	379.63​	358.74​	326​
Premium Suite Sea View	342​	347.86​	383.83​	402.88​	411.67​	390.78​	358​
Premium Suite Sea View & Swim Up	374​	379.89​	415.87​	434.92​	443.70​	422.81​	390​
Helea Suite - Pool View	454​	459.98​	495.95​	515.00​	523.78​	502.89​	470​
Helea Suite - Sea View	494​	500.02​	536.00​	555.04​	563.83​	542.94​	510​

 

[nikolacm]

The formula that I have used so far is the following (indicatively for May):
((DAYS(C$4;D$1)+1)*C5+(DAYS(D$2;D$3)+1)*D5)/(DAYS(D$2;D$1)+1)