help interpreting expression
 

greetings

The formula shown is in B5, dragged down to B10. It refers to values in
A5:A10. Could someone explain in ordinary English how I should interpret
what it is doing:

I'm particularly unsure how to interpret:
a) why the criteria for the Countif is itself a range. What does that
mean?
b) when does the logical test of the If statement return TRUE?

{=If(A5<A$5:A$10, 1/Countif(A$5:A$10, A$5:A$10))}


Sample output

A B A B
4 FALSE 3 FALSE
0 1 0 0.333333
3 1 3 FALSE
1 0.333333
8 FALSE 8 FALSE
3 1 3 FALSE

confused
anny

help interpreting expression
 

I'm just guessing, but maybe that formula is supposed to be:

=SUM(IF(A5<A$5:A$10, 1/COUNTIF(A$5:A$10, A$5:A$10),0))

then copy it down.

When you key it in, hit Control+Shift+Enter and excel will put the curly
brackets {} around the formula to indicate it is an array formula.

For a discussion on array formulas see
http://www.cpearson.com/excel/array.htm


sum(1/COUNTIF(A$5:A$10, A$5:A$10)) is a formula that I've seen on this site
used to count the number of unique values in a range. In your case, I'm
guessing the formula is supposed to count the number of unique numbers in the
range that are greater than the number being evaluated, but it would need to
be tweaked to what I've got above. The way the formula is originally
written, it won't evaluate to a singe number that can be displayed in one
cell.

"anny" wrote:

greetings

The formula shown is in B5, dragged down to B10. It refers to values in
A5:A10. Could someone explain in ordinary English how I should interpret
what it is doing:

I'm particularly unsure how to interpret:
a) why the criteria for the Countif is itself a range. What does that
mean?
b) when does the logical test of the If statement return TRUE?

{=If(A5<A$5:A$10, 1/Countif(A$5:A$10, A$5:A$10))}


Sample output

A B A B
4 FALSE 3 FALSE
0 1 0 0.333333
3 1 3 FALSE
1 0.333333
8 FALSE 8 FALSE
3 1 3 FALSE

confused
anny

help interpreting expression
 

The revised formula would give you something like:

A B
4 1 -One unique value in A5:A10 greater than 4
0 3 -Three unique values in A5:A10 greater than 0
3 2 -Two unique values in A5:A10 greater than 3
3 -Three unique values in A5:A10 greater than 0
8 0 -No unique values in A5:A10 greater than 8
3 2 -Two unique values in A5:A10 greater than 3

Is this close to what you think the formula was intended to do?




"anny" wrote:

greetings

The formula shown is in B5, dragged down to B10. It refers to values in
A5:A10. Could someone explain in ordinary English how I should interpret
what it is doing:

I'm particularly unsure how to interpret:
a) why the criteria for the Countif is itself a range. What does that
mean?
b) when does the logical test of the If statement return TRUE?

{=If(A5<A$5:A$10, 1/Countif(A$5:A$10, A$5:A$10))}


Sample output

A B A B
4 FALSE 3 FALSE
0 1 0 0.333333
3 1 3 FALSE
1 0.333333
8 FALSE 8 FALSE
3 1 3 FALSE

confused
anny

help interpreting expression
 

excellent, thank you
anny