Solved

Complex Formula Needs Revision

Posted on 2013-01-23
14
245 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
 
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 500 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now