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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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

A
  • Solved
Calculate Week commencing summary based on the daily data
Replies
2
Views
266
Adit
A
H
  • Question
Payment timeline forecast....
Replies
2
Views
361
hro5e
H
A
  • Question
Calculate Month End Dates (Weekend dates) based on the previous weekend dates and update values
Replies
3
Views
513
awoohaw
awoohaw
M
  • Question
Change value of cells in a range based on value of another cell
Replies
7
Views
115
mkrass
M
B
  • Solved
Projecting End date by accounting for holidays
Replies
4
Views
291
BB77
B

Forum statistics

Threads
1,215,098
Messages
6,123,082
Members
449,094
Latest member
mystic19

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