Calculate daily values dynamically based on the weekly forecast

A

Adit

New Member
Joined
Jul 8, 2023
Messages
15
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hi All,

I am calculating the daily forecast based on the given weekly forecast (Row 3) & daily % breakdown for the corresponding week commencing dates (Row 2).
Below is an example of the large data set and desired output (Row Output).
I have tried using index match and managed to calculate for corresponding dates however, I get 0 for dates which do not match. e.g. 21/01/2025.
Any help in the below will be really appreciated.

Excel.JPG
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
what are your formulas?
Mr. Excel has a tool called xl2bb add in (link below) that allows your to paste mini-workbooks into the post. If you cannot use that then please post your data as a copyable table, not an image. The forum needs to manually recreate your scenario to figure out your question, and that takes time and can have errors. Please help the forum help you.

If you have to use the table to copy data/expected results you will also need to give the formulas somehow.

Thanks in advance.
 
Upvote 0
Book2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1SinglesTranche 1
220/01/202527/01/202503/02/202510/02/2025
3BELGIUM698698546546
4
520/01/202521/01/202522/01/202523/01/202524/01/202525/01/202526/01/202527/01/202528/01/202529/01/202530/01/202531/01/202501/02/202502/02/202503/02/202504/02/202505/02/202506/02/202507/02/202508/02/202509/02/202510/02/202511/02/202512/02/202513/02/202514/02/202515/02/202516/02/2025
6Belgium10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%
7Output70140140140707070701401401407070705510910910955555555109109109555555
Sheet1
Cell Formulas
RangeFormula
C5:AC5C5=B5+1
B7:H7B7=$B$3*B6
I7:O7I7=$C$3*I6
P7:V7P7=$D$3*P6
W7:AC7W7=$E$3*W6
 
Upvote 0
awoohaw said:
what are your formulas?
Mr. Excel has a tool called xl2bb add in (link below) that allows your to paste mini-workbooks into the post. If you cannot use that then please post your data as a copyable table, not an image. The forum needs to manually recreate your scenario to figure out your question, and that takes time and can have errors. Please help the forum help you.

If you have to use the table to copy data/expected results you will also need to give the formulas somehow.

Thanks in advance.
Click to expand...
Thanks please see above as requested
 
Upvote 0
try this formula:


Excel Formula: 
=ROUND(XLOOKUP(B5,$B$2:$E$2,$B$3:$E$3,,-1)*B6,2)



Mr Excel Questions 71.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1SinglesTranche 1
22025-01-202025-01-272025-02-032025-02-10<<< Week Commencing Dates
3BELGIUM698698546546<<< Daily % Breakdown
4
52025-01-202025-01-212025-01-222025-01-232025-01-242025-01-252025-01-262025-01-272025-01-282025-01-292025-01-302025-01-312025-02-012025-02-022025-02-032025-02-042025-02-052025-02-062025-02-072025-02-082025-02-092025-02-102025-02-112025-02-122025-02-132025-02-142025-02-152025-02-16
6Belgium10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%
7Output69.77139.54139.54139.5469.7769.7769.7769.77139.54139.54139.5469.7769.7769.7754.61109.22109.22109.2254.6154.6154.6154.6109.2109.2109.254.654.654.6
8
9111111122
1069.77139.54139.54139.5469.7769.7769.7769.77139.54139.54139.5469.7769.7769.7754.61109.22109.22109.2254.6154.6154.6154.6109.2109.2109.254.654.654.6
11TRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE
12
Adit
Cell Formulas
RangeFormula
C5:AC5C5=B5+1
B7:H7B7=ROUND($B$3*B6,2)
I7:O7I7=ROUND($C$3*I6,2)
P7:V7P7=ROUND($D$3*P6,2)
W7:AC7W7=ROUND($E$3*W6,2)
B9:J9B9=XMATCH(B5,$B$2:$E$2,-1)
B10:Z10B10=ROUND(XLOOKUP(B5,$B$2:$E$2,$B$3:$E$3,,-1)*B6,2)
AA10:AC10AA10=XLOOKUP(AA5,$B$2:$E$2,$B$3:$E$3,,-1)*AA6
B11:AC11B11=B10=B7
 
Upvote 0
awoohaw said:
try this formula:


Excel Formula: 
=ROUND(XLOOKUP(B5,$B$2:$E$2,$B$3:$E$3,,-1)*B6,2)



Mr Excel Questions 71.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1SinglesTranche 1
22025-01-202025-01-272025-02-032025-02-10<<< Week Commencing Dates
3BELGIUM698698546546<<< Daily % Breakdown
4
52025-01-202025-01-212025-01-222025-01-232025-01-242025-01-252025-01-262025-01-272025-01-282025-01-292025-01-302025-01-312025-02-012025-02-022025-02-032025-02-042025-02-052025-02-062025-02-072025-02-082025-02-092025-02-102025-02-112025-02-122025-02-132025-02-142025-02-152025-02-16
6Belgium10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%
7Output69.77139.54139.54139.5469.7769.7769.7769.77139.54139.54139.5469.7769.7769.7754.61109.22109.22109.2254.6154.6154.6154.6109.2109.2109.254.654.654.6
8
9111111122
1069.77139.54139.54139.5469.7769.7769.7769.77139.54139.54139.5469.7769.7769.7754.61109.22109.22109.2254.6154.6154.6154.6109.2109.2109.254.654.654.6
11TRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE
12
Adit
Cell Formulas
RangeFormula
C5:AC5C5=B5+1
B7:H7B7=ROUND($B$3*B6,2)
I7:O7I7=ROUND($C$3*I6,2)
P7:V7P7=ROUND($D$3*P6,2)
W7:AC7W7=ROUND($E$3*W6,2)
B9:J9B9=XMATCH(B5,$B$2:$E$2,-1)
B10:Z10B10=ROUND(XLOOKUP(B5,$B$2:$E$2,$B$3:$E$3,,-1)*B6,2)
AA10:AC10AA10=XLOOKUP(AA5,$B$2:$E$2,$B$3:$E$3,,-1)*AA6
B11:AC11B11=B10=B7
Click to expand...
Thank you very much. Appreciate your help, works perfectly fine. Just a quick Q, let's say I have Belgium as an example, however, I have 100 different cities Is there any way I can add that as criteria to the lookup formulas instead of typing formulas manually against each city?
 
Upvote 0
Try this:

Mr Excel Questions 71.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1SinglesTranche 1
22025-01-202025-01-272025-02-032025-02-10<<< Week Commencing Dates
3BELGIUM594614617624<<< Daily % Breakdown
4GERMANY620622619614
5ENGLAND611598610622
6AUSTRIA586599604614
7FRANCE589604599601
8SPAIN585587611590
9POLAND622593603587
10ITALY604601615601
11IRELAND601620619586
12
132025-01-202025-01-212025-01-222025-01-232025-01-242025-01-252025-01-262025-01-272025-01-282025-01-292025-01-302025-01-312025-02-012025-02-022025-02-032025-02-042025-02-052025-02-062025-02-072025-02-082025-02-092025-02-102025-02-112025-02-122025-02-132025-02-142025-02-152025-02-16
14ITALY10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%
15Output60.4120.8120.8120.860.460.460.460.1120.2120.2120.260.160.160.161.512312312361.561.561.560.1120.2120.2120.260.160.160.1
16
17111111122
1860.4120.8120.8120.860.460.460.460.1120.2120.2120.260.160.160.161.512312312361.561.561.560.1120.2120.2120.260.160.160.1
19TRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE
20
21
2260.4
23
24604601615601
25
26
27
28
Adit
Cell Formulas
RangeFormula
C13:AC13C13=B13+1
B15:H15B15=ROUND($B$10*B14,2)
I15:O15I15=ROUND($C$10*I14,2)
P15:V15P15=ROUND($D$10*P14,2)
W15:AC15W15=ROUND($E$10*W14,2)
B17:J17B17=XMATCH(B13,$B$2:$E$2,-1)
B18:AC18B18=ROUND(XLOOKUP(B13,$B$2:$E$2,DROP(FILTER($A$3:$E$11,$A$3:$A$11=$A14,""),,1),,-1)*B14,2)
B19:AC19B19=B18=B15
B22B22=ROUND(XLOOKUP(B13,$B$2:$E$2,DROP(FILTER($A$3:$E$11,$A$3:$A$11=$A14,""),,1),,-1)*B14,2)
B24:E24B24=DROP(FILTER($A$3:$E$11,$A$3:$A$11=$A14,""),,1)
Dynamic array formulas.
 
Upvote 0
Solution
awoohaw said:
Try this:

Mr Excel Questions 71.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1SinglesTranche 1
22025-01-202025-01-272025-02-032025-02-10<<< Week Commencing Dates
3BELGIUM594614617624<<< Daily % Breakdown
4GERMANY620622619614
5ENGLAND611598610622
6AUSTRIA586599604614
7FRANCE589604599601
8SPAIN585587611590
9POLAND622593603587
10ITALY604601615601
11IRELAND601620619586
12
132025-01-202025-01-212025-01-222025-01-232025-01-242025-01-252025-01-262025-01-272025-01-282025-01-292025-01-302025-01-312025-02-012025-02-022025-02-032025-02-042025-02-052025-02-062025-02-072025-02-082025-02-092025-02-102025-02-112025-02-122025-02-132025-02-142025-02-152025-02-16
14ITALY10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%10%20%20%20%10%10%10%
15Output60.4120.8120.8120.860.460.460.460.1120.2120.2120.260.160.160.161.512312312361.561.561.560.1120.2120.2120.260.160.160.1
16
17111111122
1860.4120.8120.8120.860.460.460.460.1120.2120.2120.260.160.160.161.512312312361.561.561.560.1120.2120.2120.260.160.160.1
19TRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE
20
21
2260.4
23
24604601615601
25
26
27
28
Adit
Cell Formulas
RangeFormula
C13:AC13C13=B13+1
B15:H15B15=ROUND($B$10*B14,2)
I15:O15I15=ROUND($C$10*I14,2)
P15:V15P15=ROUND($D$10*P14,2)
W15:AC15W15=ROUND($E$10*W14,2)
B17:J17B17=XMATCH(B13,$B$2:$E$2,-1)
B18:AC18B18=ROUND(XLOOKUP(B13,$B$2:$E$2,DROP(FILTER($A$3:$E$11,$A$3:$A$11=$A14,""),,1),,-1)*B14,2)
B19:AC19B19=B18=B15
B22B22=ROUND(XLOOKUP(B13,$B$2:$E$2,DROP(FILTER($A$3:$E$11,$A$3:$A$11=$A14,""),,1),,-1)*B14,2)
B24:E24B24=DROP(FILTER($A$3:$E$11,$A$3:$A$11=$A14,""),,1)
Dynamic array formulas.
Click to expand...
Amazing Thank you Sir!
 
Upvote 0
I'm happy you found a solution here. Thanks and Best Wishes!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
A
  • Solved
Calculate principal amount on daily interest
Replies
5
Views
856
aurelius33
A
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
681
felixstraube
F
C
  • Question Question
How to check for value in a specific week column with duplicate name rows?
Replies
2
Views
194
KOKOSEK
KOKOSEK
A
  • Solved
Calculate Week commencing summary based on the daily data
Replies
2
Views
461
Adit
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top