# Complex Formula Needs Revision

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
LVL 1
Asked:
###### Who is Participating?

EngineerCommented:
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

Author Commented:
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.

0

Commented:
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

Author Commented:
@ Brian,

(1) Yes, you are 100% correct.

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

~ Geekamo
0

Author Commented:
@ Brian,

Not a problem.  Have a great night.  :)

~ Geekamo
0

EngineerCommented:
Here is a file with the revised formula
Workbook2.xlsm
0

Author Commented:
@ 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

Author Commented:
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

Author Commented:
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

Author Commented:
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

EngineerCommented:
So you are no longer monitoring the status DURING the shift?
0

Author Commented:
@ 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

EngineerCommented:
Are you happy with this or do you want something else.
0

Author Commented:
@ 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.