redistributing figures based on %
 

Hi

I am trying to set up a tool to help predict the total calls coming into the
workplace per day based on the % of weekly calls erceived per day of the week
(DOW) eg Mon - 25% weekly calls, Tue - 18% weekly calls etc through to Sunday
for each month. I then input a total call number for the month and it
distributes this number per day according to the percentage. What I want to
do, is be able to change my DOW % say for a Monday to less than usual, and
have the leftover redistributed through the week according to the ratio's.
EG Instead of Monday receiving 25% of weekly volumes, it might only be 10%
one week, so the remaining 15% is taken through the rest of the week weighted
accordingly. I am having troubles with this, I have been trying to use an
"if" formula in order to make the weekly total % = 100% but this doesn't seem
to be working. I don't know if I have explained this very well, but can
anyone help me?

Cheers
Soph

redistributing figures based on %
 

Assume your layout as follows:

Original %s B1:B7, called OrigPc
Modified %s, C1:C7, called ModPc
In D1 put

=IF(C1,C1,(1-SUM(ModPc))*(B1/SUMPRODUCT(--(OrigPc),--(ModPc=""))))

redistributing figures based on %
 

OOOPS! clicked Post by mistake ...

.... copy formula in my previous post down to end of week. This will
allow you to modify more than one day of the week. Of course, it
doesn't check for the situation where your modified percentages total
more than 100% ...

redistributing figures based on %
 

OOOPS! clicked Post by mistake ...

.... copy formula in my previous post down to end of week. This will
allow you to modify more than one day of the week. Of course, it
doesn't check for the situation where your modified percentages total
more than 100% ...