lookup a column value, averageif

[Fraserkitchell]

Hi there,

I have this function which is used to take a weekend and weekday average of 15 minute electric interval data for my building:
=4*(AVERAGE(IF(WEEKDAY('Electric DATA'!$K$2:$K$2975,2)> 5, IF(ROUND(MOD('Electric DATA'!$K$2:$K$2975,1)*1440,2)=D22,'Electric DATA'!$L$2:$L$2975))))

The only problem is that the WEEKDAY and ROUND(MOD( functions are hardcoded to columns. If I add more buildings, the sheet becomes unstable.

Anyone know how to have this formula look up a value in a cell, use that value to go to that column in an array of data (the value will be on top of the column), and perform this same averageif function on that column? it would help immensely...


Thanks!

 

[Fraserkitchell]

I suppose I should add an example:

so if I want to have a cell storing the value "Archives Building",
I want this function to go to another sheet full of many columns, search a 1XMany array to find Archives building, and then count many lines down from there, averaging any data from a weekday or weekend.

It almost seems like an index match but I wanted to avoid a CSE function if it isnt too complicated. I have a fear that a CSE function will take up too many resources. Not sure if that is fair or not, but I'm going with it.

Thanks
Fraser

 

[T. Valko]

I suppose I should add an example:

so if I want to have a cell storing the value "Archives Building",
I want this function to go to another sheet full of many columns, search a 1XMany array to find Archives building, and then count many lines down from there, averaging any data from a weekday or weekend.

It almost seems like an index match but I wanted to avoid a CSE function if it isnt too complicated. I have a fear that a CSE function will take up too many resources. Not sure if that is fair or not, but I'm going with it.

Thanks
Fraser

Are you trying to say that the reference to column K is a variable reference?

 

[Fraserkitchell]

Thats correct. The reference to K is variable, but above K will be a cell that I want to look up. L is also variable, but in this case the columb I need will always be one column right of the lookup column.

 

[T. Valko]

Thats correct. The reference to K is variable, but above K will be a cell that I want to look up. L is also variable, but in this case the columb I need will always be one column right of the lookup column.

Ok, this is a typical approach to what you're describing.

Let's assume this is your table in the range A1:D7

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17></TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Header1</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Header2</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Header3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>this</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">29 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">30 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">99 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>this</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">31 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">22 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">44 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>that</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">46 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">56 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">33 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>this</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">61 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">51 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">83 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>that</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">21 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">44 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">23 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>that</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">33 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">39 </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">77 </TD></TR></TBODY></TABLE>

You want the average of "this" from the column with the column header of "header2".

This array entered** version will work in any version of Excel:

=AVERAGE(IF(A2:A7="this",INDEX(B2:D7,,MATCH("header2",B1:D1,0))))

Better to use cells to hold the criteria:

• F2 = this
• G2 = Header2

=AVERAGE(IF(A2:A7=F2,INDEX(B2:D7,,MATCH(G2,B1:D1,0))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

This normally entered version will work in Excel 2007 and later:

=AVERAGEIF(A2:A7,"this",INDEX(B2:D7,,MATCH("header2",B1:D1,0)))

=AVERAGEIF(A2:A7,F2,INDEX(B2:D7,,MATCH(G2,B1:D1,0)))

 

[Fraserkitchell]

ok! This will help. Columns are not defined so I'll assume the array function will then be performed on anything within that column. Thanks and I'll give it a try.

Fraser

 

[Fraserkitchell]

Ok I tried the above and I can't get this part to work.

(AVERAGE(IF(WEEKDAY(INDEX(DATA!A1:CP2689,,MATCH(Archives!
A1,DATA!A1:CP1,0)+1)),2)<6

This is the part that asks if a timestamp in a column is a weekday or a weekend day.

My function is breaking down here. Anyone able to sort through this mess? I'm working on it over here as well, will post if I get it working.

Fraser

 

[Fraserkitchell]

Got it.

=4*(AVERAGE(IF(WEEKDAY(INDEX(DATA!$A$2:$CP$2689,,MATCH(Archives!$A$1,DATA!$A$1:$CP$1,0))),2)<6,IF(ROUND(MOD(INDEX(DATA!$A$2:$CP$2689,,MATCH(Archives!$A$1,DATA!$A$1:$CP$1,0)),1)*1440,2)=D2,INDEX(DATA!$A$2:$CP$2689,,MATCH(Archives!$A$1,DATA!$A$1:$CP$1,0)+1))))

CSE Function.

Thanks for all the help!

Fraser

 

[T. Valko]

Got it.

=4*(AVERAGE(IF(WEEKDAY(INDEX(DATA!$A$2:$CP$2689,,MATCH(Archives!$A$1,DATA!$A$1:$CP$1,0))),2)<6,IF(ROUND(MOD(INDEX(DATA!$A$2:$CP$2689,,MATCH(Archives!$A$1,DATA!$A$1:$CP$1,0)),1)*1440,2)=D2,INDEX(DATA!$A$2:$CP$2689,,MATCH(Archives!$A$1,DATA!$A$1:$CP$1,0)+1))))

CSE Function.

Thanks for all the help!

Fraser

Good deal. Thanks for the feedback!