Monthly number to be divided by dynamic values

[thetnaingsoe]

Hey everyone,

I have a table which defines what rate to be applied to each period (from cell A1 to B13).

In Row 16 and 17, there is old rate (cell A17) and new rate (cell B17). From cell C17 to N18 is monthly revenue.

Rate table defines that Jan 2023 and Feb 2023 are using the old rate and Mar 2023 to Dec 2023 are using the new rate.

Cell P17 will calculate the total hours for the year. Since Jan and Feb 2023 are using the old rates, revenue from that 2 periods are converted to hours using the old rate and Mar to Dec 2023 revenues are converted to hours using the new rate. Manual calculation will look like this =SUM(C17:D17)/A17+SUM(E17:N17)/B17

Is there a way to automate this calculation?

Thanks in advance!

 

[awoohaw]

This is a clunky formula, but I think it works:
Your formula is just as good, but it doesn't allow for changing the Old/New Rate in column B1:B13.

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Month	rate type		Hey everyone, I have a table which defines what rate to be applied to each period (from cell A1 to B13). In Row 16 and 17, there is old rate (cell A17) and new rate (cell B17). From cell C17 to N18 is monthly revenue. Rate table defines that Jan 2023 and Feb 2023 are using the old rate and Mar 2023 to Dec 2023 are using the new rate. Cell P17 will calculate the total hours for the year. Since Jan and Feb 2023 are using the old rates, revenue from that 2 periods are converted to hours using the old rate and Mar to Dec 2023 revenues are converted to hours using the new rate. Manual calculation will look like this =SUM(C17:D17)/A17+SUM(E17:N17)/B17 Is there a way to automate this calculation?
2	1/1/2023	Old Rate
3	2/1/2023	Old Rate
4	3/1/2023	New Rate
5	4/1/2023	New Rate
6	5/1/2023	New Rate
7	6/1/2023	New Rate
8	7/1/2023	New Rate
9	8/1/2023	New Rate
10	9/1/2023	New Rate
11	10/1/2023	New Rate
12	11/1/2023	New Rate
13	12/1/2023	New Rate
14
15
16	Old Rate	New Rate	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
17	$ 100	$ 150	$ 18,000	$ 17,000	$ 20,000	$ 21,000	$ 22,000	$ 22,000	$ 19,000	$ 18,000	$ 17,000	$ 20,000	$ 23,000	$ 11,000	1636.67
Sheet4

Cell Formulas
Range	Formula
O17	O17	=SUM($C$17:$N$17/XLOOKUP(INDEX($B$2:$B$13,XMATCH(C16:N16,$A$2:$A$13)),$A$16:$B$16,$A$17:$B$17))

 

[thetnaingsoe]

This is a clunky formula, but I think it works:

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Month	rate type		Hey everyone, I have a table which defines what rate to be applied to each period (from cell A1 to B13). In Row 16 and 17, there is old rate (cell A17) and new rate (cell B17). From cell C17 to N18 is monthly revenue. Rate table defines that Jan 2023 and Feb 2023 are using the old rate and Mar 2023 to Dec 2023 are using the new rate. Cell P17 will calculate the total hours for the year. Since Jan and Feb 2023 are using the old rates, revenue from that 2 periods are converted to hours using the old rate and Mar to Dec 2023 revenues are converted to hours using the new rate. Manual calculation will look like this =SUM(C17:D17)/A17+SUM(E17:N17)/B17 Is there a way to automate this calculation?
2	1/1/2023	Old Rate
3	2/1/2023	Old Rate
4	3/1/2023	New Rate
5	4/1/2023	New Rate
6	5/1/2023	New Rate
7	6/1/2023	New Rate
8	7/1/2023	New Rate
9	8/1/2023	New Rate
10	9/1/2023	New Rate
11	10/1/2023	New Rate
12	11/1/2023	New Rate
13	12/1/2023	New Rate
14
15
16	Old Rate	New Rate	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
17	$ 100	$ 150	$ 18,000	$ 17,000	$ 20,000	$ 21,000	$ 22,000	$ 22,000	$ 19,000	$ 18,000	$ 17,000	$ 20,000	$ 23,000	$ 11,000	1636.67
Sheet4

Cell Formulas
Range	Formula
O17	O17	=SUM($C$17:$N$17/XLOOKUP(INDEX($B$2:$B$13,XMATCH(C16:N16,$A$2:$A$13)),$A$16:$B$16,$A$17:$B$17))

It works perfect! Thank you so much!!!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This worked for me.

23 01 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Month	rate type
2	1/01/2023	Old Rate
3	1/02/2023	Old Rate
4	1/03/2023	New Rate
5	1/04/2023	New Rate
6	1/05/2023	New Rate
7	1/06/2023	New Rate
8	1/07/2023	New Rate
9	1/08/2023	New Rate
10	1/09/2023	New Rate
11	1/10/2023	New Rate
12	1/11/2023	New Rate
13	1/12/2023	New Rate
14
15
16	Old Rate	New Rate	1/01/2023	1/02/2023	1/03/2023	1/04/2023	1/05/2023	1/06/2023	1/07/2023	1/08/2023	1/09/2023	1/10/2023	1/11/2023	1/12/2023
17	100	150	18000	17000	20000	21000	22000	22000	19000	18000	17000	20000	23000	11000	1636.666667
Old New

Cell Formulas
Range	Formula
O17	O17	=SUM(C17:N17/IF(TRANSPOSE(B2:B13)=A16,A17,B17))