Display numbers in range

Question

I have a multicomponent question.  I know how to do lookup formulas, but these questions are a modification.

1)      Is it possible for me to give a low number and a high number and have Excel give me all numbers that fall between those numbers (including those numbers)?  Also, if that number is not found give an average of the number higher and the number lower in the sequence.

2)      Then in the row below, perform the lookup function (LOOKUP(A5,$B1:$H1,$B2:$H2)) and also if needed, average the number higher and lower (OR an extrapolation to maintain the original number). 

 For example:

                  A          B          C         D          E          F          G          H

1         Data 1   79.6     79.9      80.3     80.6      81.0     81.4      81.7

2         Data 2   0.1       0.2        0.3       0.4        0.5       0.6        0.7

3 

4                       79.7    81.3

In row 5, give the range of all numbers between the number found in B4 (79.7) and the number found in C4 (81.3).  Also, if that number is not found give an average of the number higher and the number lower as well as (row 6) the number in the corresponding row.

Result

                  A          B          C         D          E          F          G          H

1         Data 1   79.6     79.9      80.3     80.6      81.0     81.4      81.7

2         Data 2   0.1       0.2        0.3       0.4        0.5       0.6        0.7

3 

4                       79.7    81.3

5       79.75      80.3     80.6      81.2

6         0.15        0.3       0.4      0.55

---

OR

---

5       79.7     79.9      80.3     80.6      81.0     81.2

6       0.133   0.2        0.3       0.4        0.5       0.55

Thank you,

Answer 1

I'm not sure if this is what you want, but try it:

Data_1 is the Name'd Range with your first line Data

Data_2 is the Name'd Range with your second line of Data

Data_1:   =$B$1:$H$1

Data_2:   =$B$2:$H$2

A5:

=IFERROR(HLOOKUP($B$4,Data_1,1,FALSE),INDEX((

OFFSET(Data_1,0,0,1,COUNT(Data_1)-1)+OFFSET(

Data_1,0,1,1,COUNT(Data_1)-1))/2,1,MATCH($B$4,Data_1)))

A6:

=IFERROR(HLOOKUP($B$4,(Data_1:Data_2),2,FALSE),

INDEX((OFFSET(Data_2,0,0,1,COUNT(Data_1)-1)+OFFSET(

Data_2,0,1,1,COUNT(Data_1)-1))/2,1,MATCH($B$4,Data_1)))

B5:

=IFERROR(IF(INDEX(Data_1,1,MATCH($B$4,Data_1)+

COLUMNS($A:A))<=$C$4,INDEX(Data_1,1,MATCH(

$B$4,Data_1)+COLUMNS($A:A)),IF(AND(INDEX(

Data_1,1,MATCH($B$4,Data_1)+COLUMNS($A:A)-1)<=$C$4,

INDEX(Data_1,1,MATCH($B$4,Data_1)+COLUMNS($A:A))>$C$4),

INDEX((OFFSET(Data_1,0,0,1,COUNT(Data_1)-1)+OFFSET(

Data_1,0,1,1,COUNT(Data_1)-1))/2,1,MATCH($C$4,Data_1)),"")),"")

B6:

=IFERROR(IF(INDEX(Data_1,1,MATCH($B$4,Data_1)+

COLUMNS($A:A))<=$C$4,INDEX(Data_2,1,MATCH(

$B$4,Data_1)+COLUMNS($A:A)),IF(AND(INDEX(Data_1,1,MATCH(

$B$4,Data_1)+COLUMNS($A:A)-1)<=$C$4,INDEX(Data_1,1,

MATCH($B$4,Data_1)+COLUMNS($A:A))>$C$4),INDEX((

OFFSET(Data_2,0,0,1,COUNT(Data_1)-1)+OFFSET(

Data_2,0,1,1,COUNT(Data_1)-1))/2,1,MATCH($C$4,Data_1)),"")),"")

Select B5:B6 and fill right to H5:H6

Answer 2

It is the average of the number below and above:  e.g. Average(79.6,79.9)

Answer 3

Hi,

Please explain what 79.75 is?  I do not see that number anywhere in row1:2

Answer 4

bedtime for me now.  but have a look here, this should get you quite a long way.

http://www.contextures.com/xlvba01.html#Regular

Sub allllenbiocellguy()

Dim iReadCol As Integer

Dim iWriteCol As Integer: iWriteCol = 1

Dim dFrom As Double:  dFrom = Range("b4").Value

Dim dFromLast As Double

Dim bFoundFirst As Boolean

Dim dTo As Double:  dTo = Range("c4").Value

Dim dToNext As Double

Dim bFoundLast As Boolean

    For iReadCol = 2 To 8

        If bFoundFirst = False Then If Cells(1, iReadCol).Value < dFrom Then dFromLast = Cells(1, iReadCol).Value

        If bFoundLast = False Then If Cells(1, iReadCol).Value > dTo Then dToNext = Cells(1, iReadCol).Value

        If Cells(1, iReadCol).Value = dFrom Then

            bFoundFirst = True

            Cells(5, iWriteCol) = dFrom

            iWriteCol = iWriteCol + 1

        End If

        If Cells(1, iReadCol).Value > dTo Then

            bFoundLast = True

            Cells(5, iWriteCol) = dTo

            Exit Sub

        End If

        If Cells(1, iReadCol).Value > dFrom Then

            If bFoundFirst = False Then

                bFoundFirst = True

                Cells(5, iWriteCol) = dFrom

                Cells(5, iWriteCol + 1) = Cells(1, iReadCol).Value

                iWriteCol = iWriteCol + 2

            Else

                Cells(5, iWriteCol) = Cells(1, iReadCol).Value

                iWriteCol = iWriteCol + 1

            End If

        End If

    Next iReadCol

End Sub

Answer 5

Yes, it's possible.  I'd do it with VBA to churn and spit out the numbers.  It's 30 mins work.

Is B1:H1 always sorted in ascending order?

allllen