How to populate daily sales dynamically based on the week-ending dates and daily % breakdo

[Berlin007]

Hello ,

I am trying to calculate daily sales based on the week-ending dates and Daily % participation.
Below is just a sample data set for 7 weeks, I have a 52-week sales profile which I need to break down by day based on the week-day sales participation.
Also, the number of working days varies by month, e.g. In August it's a 5-day working pattern and Sept. is 6 days.
Doing it manually or copy-pasting formulas will take ages hence was looking for some help to set up a dynamic formula for populating daily sales based on the given parameters.
Please note that the week date will always be week ending dates [Sun-Sat].

Any Help will be highly appreciated.
Capture.JPG



Daily Splits.xlsxDaily Splits - TBC.xlsx

[DJunqueira]

Try this.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Berlin007]

Hello Many Thanks for the response. However, I am after the other way round solution. Break up Weekly sales into daily based on the % Splits by Day.
In the attached file I am calculating daily sales manually e.g. for 01/09/24 [Sunday] = Look into week ending sales here 7000 and multiply by the day %. And I would like to automate this process

[Berlin007]

Hello Many Thanks for the response. However, I am after the other way round solution. Break up Weekly sales into daily based on the % Splits by Day.
In the attached file I am calculating daily sales manually e.g. for 01/09/24 [Sunday] = Look into week ending sales here 7000 and multiply by the day %. I would like to automate this process

[DJunqueira]

I'm a bit confused about what is source data and what is expected result.
Could you please clarify that in the worksheet?

[Berlin007]

Sure I have re-attached the file to the original post, and have categorized the Input / Source data and the desired Output, I hope this helps

[DJunqueira]

Let's see if I got it right this time.

Please Login or Register  to view this content.

[Berlin007]

Thank you Sir for the swift response. Appreciated

[DJunqueira]

Glad to help.

[josephteh]

Alternatively, =LET(a,MATCH(B12+7-MATCH(B11,$B$11:$H$11,0),$B$2:$H$2,0),INDEX($B$3:$H$3,a)*INDEX($B$7:$H$8,MATCH(INDEX($B$4:$H$4,a),$A$7:$A$8,0),MATCH(B11,$B$6:$H$6,0))).

[Berlin007]

Thank you, Is it possible to share this with the e Excel file called Daily Splits- TBC.xlsx ?

[Berlin007]

Hey , I was trying to use the formula in the file and for some reasons the formula doesn't work for longer term , I have attached a file called daily splits - TBC .xlsx

[josephteh]

Here is the updated file.

[Berlin007]

Thank you , Much appreciated