Midpoint of list of numbers

[markdwalls]

Hi All,

I'm currently looking for a formula which find the midpoint of a two numbers and return the midpoint whilst maintaining the original numbers as well. Apologies if that explanation isn't very good. Below is a screenshot of what i'm trying to do. Column C is the raw data that i have reported. Column Q is an example of what i need the formula to return. In theory, the list of numbers in column Q should be twice as long as the list in column C.

Thanks in advance

 

[jasonb75]

See if this helps. I don't think that it will be possible to do it straight into a single column. If it can be done then it will likely require something ludicrously long and complicated.

Book1
	A	B	C
1	C	Helper	Q
2	2600		2600
3	2609.883	2604.9415	2604.942
4	2619.882	2614.8825	2609.883
5	2630.024	2624.9530	2614.883
6	2639.871	2634.9475	2619.882
7			2624.953
8			2630.024
9			2634.948
10			2639.871
Sheet1

Cell Formulas
Range	Formula
B3:B6	B3	=MEDIAN(A2,A3)
C2:C10	C2	=SMALL($A$2:$B$6,ROWS(C$2:C2))

 

[kennypete]

A very brief VBA solution with the same data in col C - no helper column needed - which could be enhanced to be more extensible, handle the last value more elegantly, or whatever:

Sub midpoints()
qr = 2
For n = 2 To 6
    f = Cells(n, 3)
    s = Cells(n + 1, 3)
    m = (f + s) / 2
    Cells(qr, 17) = f
    If n <> 6 Then Cells(qr + 1, 17) = m
    If n <> 6 Then Cells(qr + 2, 17) = s
    qr = qr + 2
Next n
End Sub

Output:

Book2
	C														Q
1	C														Q
2	2600														2600
3	2609.883														2604.942
4	2619.882														2609.883
5	2630.024														2614.883
6	2639.871														2619.882
7															2624.953
8															2630.024
9															2634.948
10															2639.871
Sheet1

 

[markdwalls]

A very brief VBA solution with the same data in col C - no helper column needed - which could be enhanced to be more extensible, handle the last value more elegantly, or whatever:

Sub midpoints()
qr = 2
For n = 2 To 6
    f = Cells(n, 3)
    s = Cells(n + 1, 3)
    m = (f + s) / 2
    Cells(qr, 17) = f
    If n <> 6 Then Cells(qr + 1, 17) = m
    If n <> 6 Then Cells(qr + 2, 17) = s
    qr = qr + 2
Next n
End Sub

Output:

Book2
	C														Q
1	C														Q
2	2600														2600
3	2609.883														2604.942
4	2619.882														2609.883
5	2630.024														2614.883
6	2639.871														2619.882
7															2624.953
8															2630.024
9															2634.948
10															2639.871
Sheet1

Thank you very much!

 

[markdwalls]

See if this helps. I don't think that it will be possible to do it straight into a single column. If it can be done then it will likely require something ludicrously long and complicated.

Book1
	A	B	C
1	C	Helper	Q
2	2600		2600
3	2609.883	2604.9415	2604.942
4	2619.882	2614.8825	2609.883
5	2630.024	2624.9530	2614.883
6	2639.871	2634.9475	2619.882
7			2624.953
8			2630.024
9			2634.948
10			2639.871
Sheet1

Cell Formulas
Range	Formula
B3:B6	B3	=MEDIAN(A2,A3)
C2:C10	C2	=SMALL($A$2:$B$6,ROWS(C$2:C2))

Thank you very much. Much appreciated