How to get the count and average of values in reference to anotherrange of data

[shriil]

Hi all,

I had posted the same in the excel worksheets group but haven't got any help yet.

I have a table as follows

Col A Col B Col C
Consumption Average Kcal Energy %
0.62 4824 8.02
0.64 4773 8.15
0.71 4137 8.23
0.685 4366 8.26
0.67 4398 8.34
0.723 4026 8.45
0.701 4111 8.40
0.69 4216 8.50
---- ---- ----
--- ---- ----

I would like to find the following

1. Count of Col A figures for specified ranges, for. e.g. (0.60-0.64); (0.64-0.67), (0.67-0.70), (0.70-0.73)

2.Average of Col B figures against the above-mentioned specified ranges of Col A

3. Minimum and Maximum of Col C figures against the above-mentioned specified ranges of Col A

Thanks in advance.

San

 

[Claus Busch]

Hi San,

Am Wed, 7 Aug 2013 10:22:50 -0700 (PDT) schrieb shriil:

1. Count of Col A figures for specified ranges, for. e.g. (0.60-0.64); (0.64-0.67), (0.67-0.70), (0.70-0.73)
=COUNTIFS(A2:A10,">="&0.6,A2:A10,"<="&0.64)

2.Average of Col B figures against the above-mentioned specified ranges of Col A
=AVERAGEIFS(B2:B10,A2:A10,">="&0.6,A2:A10,"<="&0.64)

3. Minimum and Maximum of Col C figures against the above-mentioned specified ranges of Col A

=MIN(IF((A2:A10>=0.6)*(A2:A10<=0.64),C2:C10,C2:C10))
=MAX((A2:A10>=0.6)*(A2:A10<=0.64)*(C2:C10))
the last two formulas are array formulas to enter with
CTRL+Shift+Enter.

If you have a excel version older than 2007 have a look for
SUMPRODUCT


Regards
Claus B.

 

[isabelle]

hi San,

for. e.g. 0.60-0.64

array formulas, validate with ctrl + shift + enter

=AVERAGE(IF((A2:A9>=0.6)*(A2:A9<=0.64),(B2:B9),""))

=MAX(IF((A2:A9>=0.6)*(A2:A9<=0.64),(B2:B9),""))

=MIN(IF((A2:A9>=0.6)*(A2:A9<=0.64),(B2:B9),""))

isabelle

Le 2013-08-07 13:22, shriil a écrit :

 

[shriil]

Hi San,



Am Wed, 7 Aug 2013 10:22:50 -0700 (PDT) schrieb shriil:






=MIN(IF((A2:A10>=0.6)*(A2:A10<=0.64),C2:C10,C2:C10))

=MAX((A2:A10>=0.6)*(A2:A10<=0.64)*(C2:C10))

the last two formulas are array formulas to enter with

CTRL+Shift+Enter.



If you have a excel version older than 2007 have a look for

SUMPRODUCT





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks Isabelle and Claus for your prompt help.

Both work excellently

San