Solved

# Complex Formula Needs Revision

Posted on 2013-01-23
242 Views
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.  :)

~ Geekamo
Workbook2.xlsm
0
Question by:Geekamo
• 9
• 4

LVL 1

Author Comment

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

LVL 26

Expert Comment

Hi, Geekamo.

(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

@ Brian,

(1) Yes, you are 100% correct.

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

~ Geekamo
0

LVL 1

Author Comment

@ Brian,

Not a problem.  Have a great night.  :)

~ Geekamo
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
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

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

LVL 1

Author Comment

@ 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

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

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

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

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

LVL 1

Author Comment

@ 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

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

LVL 1

Author Comment

@ 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

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.