|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
08-13-2008, 05:04 PM
|
|||
|
|||
|
If statement (array) in a chart
Hello,
I'm trying to create a line graph based on certain conditions. The data is as follows: Column A: Date Column E: Site number (100, 101, 102, etc.) Column G: Traffic volume A E G 01/08/08 100 1354 01/08/08 101 402983 ...etc. I would like to create a line graph of traffic volume during the last year, for site 100 only. I managed to write an array formula to calculate the average based on the same conditions: =AVERAGE(IF(INDEX('Entry Level'!$E:$E,(MATCH(INDEX('Entry Level'!$A:$A,COUNTA('Entry Level'!$A:$A))-365,'Entry Level'!$A:$A))): INDEX('Entry Level'!$E:$E,(MATCH(INDEX('Entry Level'!$A:$A,COUNTA('Entry Level'!$A:$A)),'Entry Level'!$A:$A)))=100, (INDEX('Entry Level'!$G:$G,(MATCH(INDEX('Entry Level'!$A:$A,COUNTA('Entry Level'!$A:$A))-30,'Entry Level'!$A:$A))): INDEX('Entry Level'!$G:$G,(MATCH(INDEX('Entry Level'!$A:$A,COUNTA('Entry Level'!$A:$A)),'Entry Level'!$A:$A)))), "")) (Don't laugh, it works!) However, I tried several things and do not seem to manage to create a graph. Let say I try to create the graph just for this site (forget about the dates restriction for a moment). I defined names "dates" and "traffic" the following way: dates =IF('Entry Level'!$E2:$E1789=100, 'Entry Level'!$A2:$A1789, "") traffic =IF('Entry Level'!$E2:$E1789=100, 'Entry Level'!$G2:$G1789, "") But it does not seem to work. Any ideas? I know it would make much more sense to try to do it in VBA but I do not know it yet... And of course, it would help if data for each site was in separate columns, but there are several dozens sites and 3 values for each so it's much more neat like it is now. Thanks, 
|
|
#2
08-14-2008, 09:23 AM
|
||||
|
||||
|
We need more detail of what did not work?
Did you get an error? Did the chart say you can not do something? Does the chart not appear as you think it should? Can you post an example?
|
|
#3
08-14-2008, 11:37 AM
|
|||
|
|||
|
Quote:
|
|
#4
08-15-2008, 10:36 AM
|
|||
|
|||
|
The chart plots some values - one along the x axis (y=0) and another along the y axis. But the conditions aren't met. It plots traffic values even if the site is not 100.
I'm trying to do it with VB now.
|
 |
| Bookmarks |
New topics in Excel Charting
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Linear Mode
