Take an average of data where lookup in array is needed

[Niels Ligtvoet]

Hi everyone,

I'm in need of a formula that finds the values displayed between two given values and take the average of corresponding data in another column.
The data in which I'm searching is organised and exposed in column D. The corresponding values are in column C.
I tried the =SUMIF( - function, but it takes no AND( - function as criteria. If this worked out, I would have divided it by te amount of rows inbetween these values.
This is the used formula:

=SUMIF('Data t2'!D610000;AND("<SBES!B35";">SBES!B36");'Data t2'!C6:C10000)

I could try to fix it with IF-functions and other sheets with helping data, but the amount of data is simply too high.

Anyone any suggestions?

Much appreciated
Thanks in advance!

 

[Niels Ligtvoet]

Or is there any way to get an array into an =AVERAGE( - function by finding the values in a MATCH( - function ?
I know it may be fantasy but somewhat like:

=AVERAGE(column ( c )+row (MATCH(F9;D610000;1) : column ( c ) + row (MATCH (F10;D610000;1) )

So the formula gets something like =AVERAGE ( C1234:C1258 )
Any thoughts on this one?



Op zondag 5 mei 2013 23:06:30 UTC+2 schreef Niels Ligtvoet het volgende:

 

[Claus Busch]

Hi Niels,

Am Sun, 5 May 2013 14:06:30 -0700 (PDT) schrieb Niels Ligtvoet:

I'm in need of a formula that finds the values displayed between two given values and take the average of corresponding data in another column.
The data in which I'm searching is organised and exposed in column D. The corresponding values are in column C.
I tried the =SUMIF( - function, but it takes no AND( - function as criteria. If this worked out, I would have divided it by te amount of rows inbetween these values.
This is the used formula:

=SUMIF('Data t2'!D610000;AND("<SBES!B35";">SBES!B36");'Data t2'!C6:C10000)

what Excel version do you use?
xl2007 or later:
=AVERAGEIFS('Data t2'!C6:C10000,'Data t2'!D610000,">="&B35,'Data t2'!D610000,"<="&B36)
other versions:
=SUMPRODUCT(--('Data t2'!D610000>=B35),--('Data t2'!D610000<=B36),'Data t2'!C6:C10000)/SUMPRODUCT(--('Data t2'!D610000>=B35),--('Data t2'!D610000<=B36),--('Data t2'!C6:C10000<>""))


Regards
Claus Busch

 

[Niels Ligtvoet]

Wow, this works!
Thank you very much!

Op zondag 5 mei 2013 23:06:30 UTC+2 schreef Niels Ligtvoet het volgende: