Thread: 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,

Originally Posted by whisky

Hello,

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.
Thanks,

Looks like you're specifying specific cell locations to find the value 100.

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.