Friday, May 10, 2013

yogi_Find Set Of Numbers From Table Such That For The Smallest Non-Zero Posive Number In Column A The One In Column B Is The Largest


                                          Google Spreadsheet   Post  #1187
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 10, 2013
user Learning Student (http://productforums.google.com/forum/?zx=gt4fhhtub7z3#!category-topic/docs/spreadsheets/ItEpHD3P8T0)
Need to determine the range using VLOOKUP
Hello,

I am trying to determine the range (i.e., the lowest value to the highest value) of positive entries (i.e., greater than zero).

I have a table with two columns (TALLY and NUMBERS) where the TALLY column contains a running count of how many times a specific number has been selected/drawn and the NUMBERS column contains the sorted values from 1 to 30 (i.e., the specific number that can be selected/drawn each time). For example…

|=======|=========|
| TALLY | NUMBERS |
|=======|=========|
|     4 |       1 |
|     1 |       2 |
|     2 |       3 |
|     6 |       4 |
|     5 |       5 |
|     0 |       6 |
|     2 |       7 |
|     0 |       8 |
|     0 |       9 |
|     0 |      10 |
|     0 |      11 |
|     1 |      12 |
|     0 |      13 |
|     0 |      14 |
|     0 |      15 |
|     … |       … |
|     n |      30 |
|=======|=========|

Anyway, I have tried various VLOOKUP formulas to return the lowest or the highest NUMBERS without much success.  Essentially I need to look at all 30 rows and determine the lowest and highest NUMBERS that has a positive (i.e., greater than zero) TALLY value.  In the example above that would be 1–12.

Any formulas or suggestions in how I might accomplish this?

Thanks in advance.
-------------------------------------------------------------------------------------------------------------------------------
let us have a look at the following