Predict Sales between two dates based on history sales per Quarter

hatahetahmad

New Member
Joined
Jan 8, 2016
Messages
31
Hi,
If I had two dates from 1/15/2018 to 7/10/2018
and I Had sales per day from historical data :
Qtr1 - 20 - per day
Qtr2 - 25 - per day
Qtr3 - 18 - per day
Qtr4 - 32 - per day

I wanna know how much I'll sold based on this data per quarter like this :
from 1/15 to 3/31 how many working days multiply Qtr 1 (65 days * 20 = 1300)
and from 4/1 to 6/30 (78 days * 25 =1950)
and from 7/1 to 7/10 (9 days * 18 =162)
I excluded Fridays only
total expected sales is (1300 + 1950 + 162 = 3412)

My solution is checking start date quarter and end date quarter then make a huge if to achieve that
Can we have more fast, dynamic and stable solution

Thank you
 
Last edited:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,409
Office Version
365
Platform
Windows
Hi, here are a couple of options that you could try to adapt to your set up - personally I think it would be better to break it up a bit (formulas in C9:G9 copied down), but I've included a one pass option I9 copied down.

Note the changes to your historical sales table to include the Quarter from and to dates.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Q</td><td style=";">Q From</td><td style=";">Q To</td><td style=";">Per Day</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Q1</td><td style="text-align: right;;">01/01/2018</td><td style="text-align: right;;">03/31/2018</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Q2</td><td style="text-align: right;;">04/01/2018</td><td style="text-align: right;;">06/30/2018</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Q3</td><td style="text-align: right;;">07/01/2018</td><td style="text-align: right;;">09/30/2018</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Q4</td><td style="text-align: right;;">10/01/2018</td><td style="text-align: right;;">12/31/2018</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Start</td><td style=";">End</td><td style="text-align: center;;">Q1</td><td style="text-align: center;;">Q2</td><td style="text-align: center;;">Q3</td><td style="text-align: center;;">Q4</td><td style="text-align: center;;">Total</td><td style="text-align: right;;"></td><td style="text-align: center;;">One Pass</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">01/15/2018</td><td style="text-align: right;;">07/10/2018</td><td style="text-align: right;background-color: #FFFF00;;">1300</td><td style="text-align: right;background-color: #FFFF00;;">1950</td><td style="text-align: right;background-color: #FFFF00;;">162</td><td style="text-align: right;background-color: #FFFF00;;">0</td><td style="text-align: right;background-color: #FFFF00;;">3412</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">3412</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">01/01/2018</td><td style="text-align: right;;">01/31/2018</td><td style="text-align: right;;">540</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">540</td><td style="text-align: right;;"></td><td style="text-align: right;;">540</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">01/01/2018</td><td style="text-align: right;;">12/31/2018</td><td style="text-align: right;;">1540</td><td style="text-align: right;;">1950</td><td style="text-align: right;;">1422</td><td style="text-align: right;;">2528</td><td style="text-align: right;;">7440</td><td style="text-align: right;;"></td><td style="text-align: right;;">7440</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">04/01/2018</td><td style="text-align: right;;">07/01/2018</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1950</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1968</td><td style="text-align: right;;"></td><td style="text-align: right;;">1968</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">01/01/2018</td><td style="text-align: right;;">01/10/2018</td><td style="text-align: right;;">180</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;">180</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">12/31/2018</td><td style="text-align: right;;">01/01/2019</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">32</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Data</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C9</th><td style="text-align:left">=MAX(<font color="Blue">0,NETWORKDAYS.INTL(<font color="Red">MAX(<font color="Green">$B$2,$A9</font>),MIN(<font color="Green">$C$2,$B9</font>),16</font>)</font>)*$D$2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D9</th><td style="text-align:left">=MAX(<font color="Blue">0,NETWORKDAYS.INTL(<font color="Red">MAX(<font color="Green">$B$3,$A9</font>),MIN(<font color="Green">$C$3,$B9</font>),16</font>)</font>)*$D$3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E9</th><td style="text-align:left">=MAX(<font color="Blue">0,NETWORKDAYS.INTL(<font color="Red">MAX(<font color="Green">$B$4,$A9</font>),MIN(<font color="Green">$C$4,$B9</font>),16</font>)</font>)*$D$4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F9</th><td style="text-align:left">=MAX(<font color="Blue">0,NETWORKDAYS.INTL(<font color="Red">MAX(<font color="Green">$B$5,$A9</font>),MIN(<font color="Green">$C$5,$B9</font>),16</font>)</font>)*$D$5</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G9</th><td style="text-align:left">=SUM(<font color="Blue">C9:F9</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I9</th><td style="text-align:left">{=SUMPRODUCT(<font color="Blue">IF(<font color="Red">A9<=$C$2:$C$5,IF(<font color="Green">B9>=$B$2:$B$5,NETWORKDAYS.INTL(<font color="Purple">IF(<font color="Teal">$B$2:$B$5>$A9,$B$2:$B$5,A9</font>),IF(<font color="Teal">$C$2:$C$5<$B9,$C$2:$C$5,B9</font>),16</font>)</font>)</font>),$D$2:$D$5</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,102,360
Messages
5,486,388
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top