Calgarypuck Forums - The Unofficial Calgary Flames Fan Community
Old 11-23-2016, 05:22 PM   #21
calumniate
Franchise Player
 
calumniate's Avatar
 
Join Date: Feb 2007
Location: A small painted room
Exp:
Default

Quote:
Originally Posted by RW99 View Post
So I'm back with another question for the CP braintrust, any help would be appreciated. I have this spreadsheet:



The green section is fairly straight forward, I have to distribute files every day to a group of six staff. Today I am distributing 1000 files (D2). Of these six staff, Shannon and Greg are slackers, so they only get 10.6% of the files, everyone else is even at 19.7% (A5:A10).

The yellow section is where it gets tricky. Some staff are here a full day and some are half days and some call in sick to play Battlefield 1 (come on Terry...). So the question is what would my formula look like in the red section (H5:H10) so that regardless of who shows up to work all 1000 files get assigned?

I think I'm asking for a lot here, I'm assuming the formula might be fairly long
Since Terri is off your percentage dist has to be refactored , and compared against the sum of available workload days? On phone so didn't check your file, but 10%x0 work days equals 0
calumniate is offline   Reply With Quote
The Following User Says Thank You to calumniate For This Useful Post:
Old 11-23-2016, 05:28 PM   #22
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

I've got it figured for you, but it's dinner time. Will post solution soonish.
__________________
Air Canada - We're not happy until you're not happy.
Telus - hold on tight, we're going in dry!
Fuzz is online now   Reply With Quote
The Following User Says Thank You to Fuzz For This Useful Post:
Old 11-23-2016, 06:11 PM   #23
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

I'm not sure you can do it all in one cell, as you need to sum a new column first.
So make H=B5*G5 and fill down

Then new column I

I=H5/SUM(H$5:H$10)*D$2

If you have more rows, just change the H$10 in this formula to whatever the last row is.
__________________
Air Canada - We're not happy until you're not happy.
Telus - hold on tight, we're going in dry!
Fuzz is online now   Reply With Quote
The Following 2 Users Say Thank You to Fuzz For This Useful Post:
Old 11-23-2016, 07:09 PM   #24
RW99
First Line Centre
 
RW99's Avatar
 
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 202 203 208 216 217 219 221 222 225 313 317 Heritage Classic G
Exp:
Default

Thanks a bunch, I was thinking of a much more complicated formula. Math ftw.
RW99 is offline   Reply With Quote
Old 05-14-2017, 09:37 PM   #25
EldrickOnIce
Franchise Player
 
EldrickOnIce's Avatar
 
Join Date: Jul 2002
Location: YUL
Exp:
Default

Wow I am no good at excel. I want to do something pretty simple i think, but maybe not and regardless, I have no idea how to do it.
I have several tasks that will regularly take place: task1, task2, task3,...
These tasks will alternate in the same 'activity' column (column B)
Each of these tasks will take a varying amount of 'time', and that be recorded next to the activity in column C.
Can I have excel automatically calculate the total hours spent on each specific activity?

thanks
__________________
Now firmly on the Preds 2017 playoff bandwagon
Quote:
ďI canít wait for the crowd, the noise, the energy in the building.
I canít wait to take that all away from them.Ē
PK Subban
EldrickOnIce is offline   Reply With Quote
Old 05-14-2017, 11:40 PM   #26
sureLoss
Some kinda newsbreaker!
 
sureLoss's Avatar
 
Join Date: May 2004
Location: Learning Phaneufs skating style
Exp:
Default

Quote:
Originally Posted by EldrickOnIce View Post
Wow I am no good at excel. I want to do something pretty simple i think, but maybe not and regardless, I have no idea how to do it.
I have several tasks that will regularly take place: task1, task2, task3,...
These tasks will alternate in the same 'activity' column (column B)
Each of these tasks will take a varying amount of 'time', and that be recorded next to the activity in column C.
Can I have excel automatically calculate the total hours spent on each specific activity?

thanks
If I understand your problem correctly, sounds like the SUMIFS function.

https://support.office.com/en-us/art...6-611cebce642b

=SUMIFS(C1:C100, B1:B100, "=task1")

for example would add up all the values in column C where column B is "task1"

You can also alternatively use pivot tables as a quick solution too.

Last edited by sureLoss; 05-14-2017 at 11:42 PM.
sureLoss is offline   Reply With Quote
The Following User Says Thank You to sureLoss For This Useful Post:
Old 05-15-2017, 07:41 PM   #27
EldrickOnIce
Franchise Player
 
EldrickOnIce's Avatar
 
Join Date: Jul 2002
Location: YUL
Exp:
Default

^ Thanks. Both worked perfectly
__________________
Now firmly on the Preds 2017 playoff bandwagon
Quote:
ďI canít wait for the crowd, the noise, the energy in the building.
I canít wait to take that all away from them.Ē
PK Subban
EldrickOnIce is offline   Reply With Quote
Reply

Tags
excel

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 02:55 PM.

Calgary Flames
2016-17




Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright Calgarypuck 2016