Hi,
I have a column and data input value and need to determine the row output.
Table:
_______|40 60 80 100
(1/2)___|4 3 2 2
(3/4)___|8 6 6 6
(3/4)___|10 8 6 6
(2-1/2)_|16 13 12 12
The column and row indicies are determined as follows:
If column input value = column value then column index = column number
If column input value > column value then column index = next column number
If column input value > max column value the column index = max column number
If data input value = data value then row index = index of data value in column index
If data input value > data value then row index = next index of data value in column index
If data input value > max data value then row index = max index of data value
Please note that the data values and row output are not unique.
Here is some sample input and how the output is determined from the table:
Column Input-->actual | Data Input-->actual | Row Output
40-->40 | 4-->4 | (1/2)
41-->60 | 4-->6 | (3/4)
65-->80 | 3-->6 | (3/4)
80-->80 | 2-->2 | (1/2)
81-->100 | 2-->2 | (1/2)
85-->100 | 7-->12 | (2-1/2)
I hope my description of the problem is sufficient. I have been struggling with this for a week so any help would be greatly appreciated. Excel functions would be nice but VBA code would also work. Thanks.