# large()

1. ### Combining "if", "Large" and "small" in the same formula

I can not seem to get the formula correct for the following scenario. If any number in column a, rows 1-3 is greater than zero, return the largest number. However, if the numbers are all less than zero, return the smallest number. I tried =IF(A1:A3>0,LARGE(A1:A3,1),SMALL(A1:A3,1)). I continue...
2. ### Return cell address of value in array

Excel 97 A 40 row two column list of items made up of (col a) a text label (say apples & (col b) a count of the occurances of the text label in an external table. - all ok upto this point Need to identify the top 10 text counts, so say the most common item occurs 15 times, I can identify...
3. ### Formula Help: Find the largest number that ends with a certain digit

I know the =LARGE() function finds the kth largest number in an array. I need to build a formula that finds the kth largest number in an array - when the number ends with a specified digit. Data like this: 11 51 31 21 41 would give the result 51, if I'm looking for numbers ending in 1. This: 7...