Formula Understanding

[vijay.vizzu]

Hi... Excel Ninjas


Can you all please explain the below formulas, how the formula is behaving like chandoo examples. Explains step by step.

=IF(COUNTIF(DataEntry!$B$2:$B$7,A1)>=1,"",ROW())

=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))

=IF(COUNTIF(DataEntry!$B$2:$B$7,E1)>=1,"",ROW())

=IF(ROW(E1)-ROW(E$1)+1>COUNT(F$1:F$10),"",INDEX(E:E,SMALL(F$1:F$10,1+ROW(E1)-ROW(E$1))))

=IF(COUNTIF(DataEntry!$B$2:$B$7,I1)>=1,"",ROW())

=IF(ROW(I1)-ROW(I$1)+1>COUNT(J$1:J$10),"",INDEX(I:I,SMALL(J$1:J$10,1+ROW(I1)-ROW(I$1))))

Thanks

 

[Hui]

=IF(COUNTIF(DataEntry!$B$2:$B$7,A1)>=1,"",ROW())

If the number of cells with values in DataEntry!$B$2:$B$7 is greater than or equal to the value of Cell A1, put "" (Nothing) else put the Row Number


=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))

If the number of Rows between the top of the sheet and the current cell is greater than the number of entries in B1:B6 put "" (nothing) else put a value from Column A that is equivalent to the Nth smallest value in Range B1:B6, where N is the number of rows between the top of the sheet and where the formula is


The rest of the formulas are similar