Daily projection based on historical percentages

[MDL1323]

I'm looking to get a formula for end of day projection for order lines based on historical percentages for each of our hourly order pulls. Below is an example:

6am pull - 100 lines (historically 50% of daily volume)
7am pull - 50 lines (historically 25% of daily volume)
8am pull - ?? (historically 15% of daily volume)
9am pull - ?? (historically 10% of daily volume)
What is the end of day line projection?

Assuming the percentages are stored in cells A1:D4

Would anyone please be able to help me out with this?

 

[wat24]

Hi there. I assumed you had a table set up with Hour of day in A1, Historical % in B1, Actual Lines in C1, and Estimated lines in D1 as headers. Actual lines you can type in each time after your pull, the hour of day plus historical percentages are set to your defined amounts.

In the estimated lines column, cell d3 - use an IF statement: IF(C3 = "", $D$7 * B3, "") and send it down.

Below the table, in Cells C7 sum the actuals. In D7 this your end of day estimate and where another if statement was used looking to see if actuals was blank for the hours and if not create an estimate
Here is the formula for D7: If(AND(C5="", C4="", c3=""), C2/B2, IF(AND(C5="", C4=""), (C2+C3)/(B2+B3), IF(C5="", (C2+C3+C4)/(B2+B3+B4), "")))

The formula set up this way will allow it to still work if the historical percentages in your table change or you make them dynamic.
Reach out if you have any questions or it is not clear. This is my first attempt helping out.