Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seems simple but no solution so far
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
!!!!!!PLEASE HELP ME!!!!!! I'm sure it's a simple solution... | Excel Discussion (Misc queries) | |||
Is there a simple solution? | Excel Worksheet Functions | |||
Macros in Excel(probably a simple solution) | Excel Discussion (Misc queries) | |||
Surely a simple solution exists | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) |