Harvey Waxman said:
What about the last entry in the extra cell, where there is no bin?
If you choose to have no bin in the last cell (I do, just for
completeness), use (array-entered)
=AVERAGE(IF($A$1:$A$100>90,$A$1:$A$100))
where 90 is the value in your highest bin.
But more importantly could you describe in words what's happening?
Make sure you take a look at Help, but in this case, the SUMIF() looks
at all the values in A1:A100, and if their value is less than or equal
to 10, sums them. That sum is divided by the value returned by Frequency
for your first bin - i.e., the number of cells with values <=10. You
could use
=SUMIF($A$1:$A$00,"<=10",$A$1:$A$100)/C1
to specify that A1:A100 is the range to be added, but the default is to
add the same range as the criterion is evaluated over.
I never understood the SUMIF and SUMPRODUCT functions. Whence this: "<=10"?
In this formula ($A$1:$A$100>B1) generates an array of 100 TRUE/FALSE
values, depending on whether the cells in A1:A100 are greater than B1.
SUMPRODUCT() only works with numeric arrays, so it can't use the boolean
array directly. The double unary minus first coerces each element of
that array to -1/0s, respectively, then to 1/0.
Similarly, the --($A$1:$A$100<=B2) creates a 100 element array of 1/0s.
SUMPRODUCT then multiplies these arrays together to produce a third
array - if both of the elements of the first two arrays are 1, then the
corresponding element of the third array will equal 1 (1*1). A zero in
either element will result in a zero in the third array. That array is
then multiplied by the array $A$1:$A$100 producing a final array that
contains the values in A1:A100 that meet both criteria. That array is
then summed.
You might want to look here for another explanation:
http://www.mcgimpsey.com/excel/variablerate1.html
I'm scratching my head
Translate please
By array entering this formula, Each element of $A$1:$A$100 is compared
to B1, returning a boolean TRUE/FALSE. For each element that is TRUE,
the corresponding value in the TRUE branch of the IF() is returned. The
False branch (which I've left out here) by default returns FALSE.
AVERAGE ignores boolean values, so only values which meet the criterion
are averaged.
Is there an extra "(" after the IF? And translate also.
No, the ( is necessary - here you're multiplying 2 arrays:
($A$1:$A$100<=B2) and ($A$1:$A$100>B1)
Since * has a higher priority than <=, the comparisons need to be
enclosed in parens.
