Seems simple but no solution so far

[Sean]

Hi guys,

In my last post the problem was to get the dates in the data to become
categories for x-axis. I have now managed to do so, however, here is
the problem. I have data in this format

Date Day Hours Productivity
Efficiency
25/05/2007 Friday 0 0 0
25/05/2007 Friday 0 0 0
25/05/2007 Friday 0 0 0
25/05/2007 Friday
26/05/2007 Saturday
26/05/2007 Saturday
26/05/2007 Saturday 5 20% 0.05
26/05/2007 Saturday
27/05/2007 Sunday
27/05/2007 Sunday 2 5% 0.03
28/05/2007 Monday
28/05/2007 Monday 6.5 10% 0.04


As is evident there are multiple rows with the same date and day. I
have managed to create a graph but the problem is that graph only
shows the maximum values (clumn chart). When I use a line chart it
gives me something that I cannot make sense of. The best way of
dealing with this situation I have found is to display the total of
hours, average of productivity and efficiency. I think the only way to
do so is to create a function that does this:

function Hours(date vector, hours vector) as Variant()
Dim Arr()
ReDim Arr(UBound(date)
local_date=date(0)
local_hours=hours(0)
index=0
for i=1 to UBound(date)
if local_date==date(i)
count++ //used to find avg in
efficiency, productivity
local_hours+=hours(i)
else
local_date=date(i)
count=0
Arr(index)=local_hours
local_hours=0
index++
end if
Hours = Arr
end Hours

Similar functions can be made for productivity and efficiency. Then I
can write the following in the Source Data tab Y-Values field :
=Hours('Daily Log'!$A$1:$A$50,'Daily Log'!$C$1:$C$50). Is this
correct?

Anyways propelled with my enthusiasm for my first VBA program, I
started making my own function that returns a vector of values (i.e.
one dimensional array) and failed.
I keep getting #VALUE! with the above function. What is wrong with
this. I must add that I take input from a column so I do a transpose
and also transpose the result. Is this correct?

Thanks for any help in this.

Sean

P.S. Can the chart wizard do this thing itself?