Goal Seek using Time

[laya1024]

Hi-

I am doing a project that I am having difficulty with. Hope you can help.

I am trying to use goal seek to see with the fourth quarter target needs to be in order to meet a yearly goal. The measurement is average time on a call. So the data looks like:

Q1 - 00:03:52
Q2 - 00:05:25
Q3 - 00:04:18
Q4 -
Average needs to be 00:04:30 for all four quarters to hit target. So I need the output to be a time in the Q4 cell. in the average cell, I used a formula to get the average of all 4 quarters. I used goal seek with the set value being the average cell, the to value being 00:04:30 and by changing cell is the empty Q4 cell. When I press ok, the goal seek status box appears and the target value has changed to 0.003125, and current value is 0:03:24. A 0 appears in the Q4 cell. I was expecting the Q4 cell to have a format of hh:mm:ss and it very well should be greater than zero. And the average should be 00:04:30 not 00:03:24. What am I doing wrong and how can I fix it?

[martindwilson]

have you tried converting it to seconds first? time in a1 then say in B1
=(A1*1440)*60 (or A1*86400)formated as general
convert result back using (if result is in C1)
=c1/86400 formatted as hh:mm:ss

[laya1024]

Thanks for the help. I attempted to convert the time to seconds but that still didn't work. The formatting is still incorrect. Any other suggestions?

[martindwilson]

yoy dont need goal seek
if youput Q1 in a1, Q2in a2,Q3in a3 and your target time in B1(all formated as hh:mm:ss)
Then put

=4 * B1 - SUM(A1:A3)

formated as hh:mm:ss in A4
it will return

00:04:25
which is the time needed to make your average
00:04:30(B1)
That simplifies to
=(B1*4) -SUM(A1:A3) no need to convert!