Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Complex Formula Needs Revision

Posted on 2013-01-23
14
Medium Priority
?
256 Views
Last Modified: 2013-01-26
Hello Experts,

I've attached the file I'm working on.  Please bare with me as I explain my problem.

The formula I am having trouble with G12

And more specifically, the part of the formula that is causing me trouble is:

SUM(CallLog)/SUM(End-Start-Exclude)*60

As of right now, the formula returns the correct value.

It calculates average minutes talked per hour.

IE:  If you started at 12, and ended at 5 = You worked a total of 5 hours.

If you made 4, 30 minute phone calls - you talked for a total of 2 hours, out of the 5 hours you were at work.  Therefor, you averaged 24 minutes per hour of talk time.

Hopefully, I haven't lost you...

Now, this formula is great - but it has one major flaw.  This calculation is based off of the total time you're on the clock.

For example, If I started at 12pm, made 1 phone call which lasted 24 minutes, and the current time is 12:24 pm, then my average talk time per hour SHOULD BE 24 minutes.  But, with the current formula, it would report something lower cause it's looking at the end time.

If the time is currently after the end time, then yes calculate like normal.  But if I'm still working during my shift, then the calculation should be looking at NOW(), not end.

(I previously had a EE member help me through this, taking into account the NOW() - but I've since updated the spreadsheet from scratch and I'm basically starting over.  I attempted to take the previous answer and basically change up the references, but none of the results were what I was expecting.  So I figured it was best to put back in the basic formula, and post the workbook here.

If you have any questions, just ask - I plan on staying up a lil longer.  Kinda want to write this off my list before I head to bed.  :)

Hopefully that made sense...

Thank you in advance!

~ Geekamo
Workbook2.xlsm
0
Comment
Question by:Geekamo
  • 9
  • 4
14 Comments
 
LVL 1

Author Comment

by:Geekamo
ID: 38813169
I was attempting to use this...

=SUM(CallLog)/(MIN(NOW()-TODAY(),End)-Start-Exclude)*60

Which is a slight variation of the solution an EE member posted.  But when I use it, it results in a negative value.

Talk Time
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38813209
Hi, Geekamo.

Couple of questions, please.

(1) In your 12:00 start example, you mention using Now() instead of End. But that would give you 100% talk time. Shouldn't you be using the end of the current hour as your End? Which would give you 24 minutes talk per hour.

However, that leads on to the next quesion...
(1) Will Start and End times always be on the hour? The half-hour? Any time?

Edit: It's way past my bed-time, so I'll be calling it quits for now. I hope you don't mind this brief encounter - but I think it's worthwhile as anyone getting involved in this question will need these questions answered.

Thanks,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38813223
@ Brian,

(1) Yes, you are 100% correct.  

(2) Start, End & Exclude can be any time value.

~ Geekamo
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Geekamo
ID: 38813227
@ Brian,

Not a problem.  Have a great night.  :)

~ Geekamo
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 38813384
You are getting the negative number because the now() is returning the date and time for the next day.

The now time is LESS than the START time so excel sees that you have not started work yet and the formula is subtracting a smaller time from a larger time

0055 hrs - 1200 hrs

which will give a negative result.

One way to solve this is to enter the working date in a cell (mannually; not the formula today()) and subtract the cell from now; instead of now()-today()
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38813407
Here is a file with the revised formula
Workbook2.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38821225
@ ssaqibh,

After I read your solution, I'll admit I still didn't get it.  lol I just barely grasp all of this.  Anyhow, since then I've been spending my time figuring out a solution, and I *think* this might work.  I'm posting my latest version of this workbook, if you could please review the formula I made - as I'm sure there's got to be some flaw in my logic of thinking this through.

If the formula works, then great!  But if you see a problem with my approach, then I'm back at the begining.  :)

~ Geekamo
Talk-Time-Calculator.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38821230
The formulas highlighted in yellow, is the formula I am going to use going forward.  You will also see a time line of hours, which I used those time values as the "FAKE" NOW() reference.  Idk, that was my way of trouble shooting...

~ Geekamo
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38821262
Actually, I revised the formula even further...

=ROUNDDOWN(IF(AND(NOW()-TODAY()>=Start,NOW()-TODAY()<=End),SUM(CallLog)/SUM(NOW()-TODAY()-Start-Exclude)*60,SUM(CallLog)/SUM(End-Start-Exclude)*60),0)&" TPH"
Talk-Time-Calculator.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38821289
Latest version attached, removed all helper formulas and applied the formula to B12.  I think I'm good to go with this.  If anyone sees a flaw with my formulas, please let me know.

~ Geekamo
Talk-Time-Calculator.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38821386
So you are no longer monitoring the status DURING the shift?
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38821426
@ ssaqibh,

Hmm, I think I still am...

If I understand my formula correctly, I am calculating the average TPH one way, if the NOW() time is in-between the Start & End time. And it calculates another way if the NOW() time is before the Start or after the End.

~ Geekamo
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38821657
Are you happy with this or do you want something else.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38823205
@ ssaqibh,

I think this works.  I've been testing it and it appears to be calculating as I expect. The real test will be on Monday when I have a few users using it.  But I am hoping, this is the last edit to that formula.

~ Geekamo
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question