Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help interpreting expression
excellent, thank you
anny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expression for Macro - help | Excel Discussion (Misc queries) | |||
Excel expression {=SUm(if(istext(c8:k8),1,0))} | Excel Worksheet Functions | |||
Can Excel represent formula in textural format with values substi. | Excel Worksheet Functions | |||
R² expression for trendline in chart | Charts and Charting in Excel | |||
Excel Expression | Excel Discussion (Misc queries) |