Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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="")))) |
#3
|
|||
|
|||
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% ... |
#4
|
|||
|
|||
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% ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing data in different worksheet based on month name | Excel Worksheet Functions | |||
Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) | |||
calculate minus figures | Excel Discussion (Misc queries) | |||
Calculate minus figures only | Excel Discussion (Misc queries) | |||
Counting an Array based on a calculation | Excel Discussion (Misc queries) |