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? I already tried to convert to seconds, that wasn't quite working either. HELP! Thanks!

You need to format the number 0.003125 which represents a fraction of a day.

If A1 = 0.003125

In spreadsheet
=Text(A1,"hh:mm:ss")

In VBA
MyTime = 0.003125
TextTime = format(MyTime,"hh:mm:ss")




"laya1024" wrote:


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? I already tried
to convert to seconds, that wasn't quite working either. HELP!
Thanks!




--
laya1024

First I also got the same result as you.

Then I started with a new shee, formtted col A as Cutsom-h:mm:ss, entered
the values you have given and got the right answer - 4:25:00 in cell B4...

btw 0.003125 translates to 1/0/1900 12:04:30 AM...
As you probably know Excel stores dates as a number with 1 being 1-Jan-1900
so when you enter 03:52 in a cell you are actually entering 1/0/1900
12:03:52 AM


"laya1024" wrote:


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? I already tried
to convert to seconds, that wasn't quite working either. HELP!
Thanks!




--
laya1024