How to average values in mulitple columns based on criteria.

[daveus]

Hey my excel guru friends.

I need some help. I have something very similar to a recent post, but i want to take it a little farther. I many rows of data with different column values like this:

Link Lane Section Link ID v(0) Volume(0)
4 1 1 411 10.37 1183.67
4 2 1 421 7.94 1181.66
4 3 1 431 25.14 2315.05
4 4 1 441 22.80 2065.21
4 5 1 451 18.37 2079.25
4 6 1 461 24.59 1610.99
4 7 1 471 62.84 964.98
5 1 1 511 50.67 2033.01
5 1 2 512 63.10 2128.86
5 1 3 513 63.32 2157.63
5 1 4 514 63.15 2189.18
5 1 5 515 62.89 2188.61
5 1 6 516 62.57 2160.65
5 1 7 517 62.52 2122.58
5 1 8 518 62.24 2093.38
5 1 9 519 62.37 2059.59
5 1 10 5110 62.44 2035.54
5 2 1 521 54.06 2460.58
5 2 2 522 62.75 2370.67
5 2 3 523 63.02 2324.87
5 2 4 524 63.08 2276.89
5 2 5 525 63.02 2254.17
5 2 6 526 63.25 2244.25
5 2 7 527 63.31 2248.69
5 2 8 528 63.21 2233.35
5 2 9 529 63.11 2228.04
5 2 10 5210 63.17 2222.06
5 3 1 531 48.87 2066.98
5 3 2 532 60.18 1912.76
5 3 3 533 61.94 1893.75
5 3 4 534 62.98 1891.55
5 3 5 535 63.36 1919.38
5 3 6 536 63.62 1947.09
5 3 7 537 63.73 1966.34
5 3 8 538 63.39 1988.87
5 3 9 539 63.42 2008.87
5 3 10 5310 63.66 2021.01

So, in column 4 there is an id created from the concatenation of columns 1, 2, and 3.

I want to be able to average values for a range of sections (column 3). for example, i want the average of column 5 values that meet the following criteria:

link 5 (column 1), all lanes (column 2), and sections 3-7 (column 3).

This may be better done with VBA, so a solution either way would be appreciated. It can also take several cells if needed. Please tell me if this doesn't make sense. Thanks in advance for your time!

Dave...

 

[fairwinds]

Re: How to average values in mulitple columns based on crite

Hi,

This is a little general formula:

=AVERAGE(IF(ISNUMBER(MATCH(A2:A38,H3:H9,0))*ISNUMBER(MATCH(B2:B38,I3:I9,0))*ISNUMBER(MATCH(C2:C38,J3:J9,0)),E2:E38))


You change the criterias you wnat to include in the tables above the formula.

The formula needs to be confirmed with Ctrl + Shift + Enter.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Link	Lane	Section	Link	v(0)	Volume(0)
2	4	1	1	411	10.37	1183.67		Link	Lane	section
3	4	2	1	421	7.94	1181.66		5	1	3
4	4	3	1	431	25.14	2315.05			2	4
5	4	4	1	441	22.8	2065.21			3	5
6	4	5	1	451	18.37	2079.25			4	6
7	4	6	1	461	24.59	1610.99			5	7
8	4	7	1	471	62.84	964.98			6
9	5	1	1	511	50.67	2033.01			7
10	5	1	2	512	63.1	2128.86
11	5	1	3	513	63.32	2157.63
12	5	1	4	514	63.15	2189.18
13	5	1	5	515	62.89	2188.61			63.051
14	5	1	6	516	62.57	2160.65
Sheet5

 

[daveus]

thanks! can anyone add to this so that i only have to enter a high and low for the section because some of them have sections from 2-100 and i'd rather enter just the max min instead of each section number (98 in this example).

any other thoughts? Thanks all! You guys continually amaze me!

Dave...

 

[fairwinds]

Re: How to average values in mulitple columns based on crite

Like this?

=AVERAGE(IF((A2:A38>=H3)*(A2:A38<=H4)*(B2:B38>=I3)*(B2:B38<=I4)*(C2:C38>=J3)*(C2:C38<=J4),E2:E38))

confirmed with Ctrl + shift + enter.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Link	Lane	Section	Link	v(0)	Volume(0)
2	4	1	1	411	10.37	1183.67		Link	Lane	section
3	4	2	1	421	7.94	1181.66	MIN	5	1	3
4	4	3	1	431	25.14	2315.05	MAX	5	7	5
5	4	4	1	441	22.8	2065.21
6	4	5	1	451	18.37	2079.25
7	4	6	1	461	24.59	1610.99
8	4	7	1	471	62.84	964.98
9	5	1	1	511	50.67	2033.01
10	5	1	2	512	63.1	2128.86
11	5	1	3	513	63.32	2157.63
12	5	1	4	514	63.15	2189.18
13	5	1	5	515	62.89	2188.61			62.973
14	5	1	6	516	62.57	2160.65
15	5	1	7	517	62.52	2122.58
16	5	1	8	518	62.24	2093.38
17	5	1	9	519	62.37	2059.59
18	5	1	10	5110	62.44	2035.54
Sheet5

 

[daveus]

AWESOME! Thanks so much!