# Finding nearest values in column, twice (Full Width Half Maximum)

#### coregis

##### New Member
Is there a simple way to find the Full Width Half Maximum of an indexed column? In the example data below, X column is the position and Y column is the value. I should also note the column lengths will vary. They will always be shaped roughly like a bell curve.

The value I would be interested in is 75, which is halfway between the min and max of column Y. That I can find simply enough, =MIN(B:B)+(MAX(B:B)-MIN(B:B))/2 . But then I need to find where the data crosses it both on the rising part of the curve, and on the falling part of the curve.

The results I want is are two sets of interpolations between the two values closest to the target, so in this case I'd interpolate between 3 and 4 and again between 7 and 8 where the column Y values 'cross' the target of 75 and end up with 3.25 and 7.75, Something like =FORECAST(75,A4:A5,B4:B5) and =FORECAST(75,A8:A9,B8:B9), but I would not know ahead of time what values to interpolate from. Than I can find the distance between the two interpolated values for a final answer of 4.5.

 X Y 1 50 2 50 3 70 4 90 5 100 6 100 7 90 8 70 9 50 10 50

<tbody>
</tbody>

The data will be much longer and not contain such neat and symmetrical values. Probably very few if any integers.

My thoughts seem to be pretty heavy handed and overly complicated- I seem to have to cut the data in half near the midpoint. I was hoping to find a more simple solution to the problem, but I don't know what formulas would be helpful. Thanks.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### deanst

##### Board Regular
Hi - does this help? I would post a file but that is frowned upon - so I've pasted a html file with the formulas in it.

Excel 2010
ABCDEF
2XYMax100
3150Min50
4250Average75
5370
6490Location of Last Max6
75100
86100Rising CrossoverFalling Crossover
9790Position (n-1)37
10870Position 48
11950Value (n-1)7090
121050Value 9070
13Interpolated3.257.75
14
15Distance4.5

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=MAX(\$B\$3:\$B\$12)
E3=MIN(\$B\$3:\$B\$12)
E4=AVERAGE(E2:E3)
E6=MATCH(2, INDEX(1/(\$B\$3:\$B\$12=\$E\$2), 0), 1)
E9=MATCH(\$E\$4, \$B\$3:\$B\$12, 1)
F9=MATCH(\$E\$4, OFFSET(\$B\$2, \$E\$6, 0, ROWS(\$B\$3:\$B\$12) - \$E\$6 + 1), -1) + \$E\$6 - 1
E10=E9+1
F10=F9+1
E11=OFFSET(\$B\$2, E9, 0)
F11=OFFSET(\$B\$2, F9, 0)
E12=OFFSET(\$B\$2, E10, 0)
F12=OFFSET(\$B\$2, F10, 0)
E13=(\$E\$4-E11)/(E12-E11) + E9
F13=(\$E\$4-F11)/(F12-F11) + F9
A4=A3+1
A5=A4+1
A6=A5+1
A7=A6+1
A8=A7+1
A9=A8+1
A10=A9+1
A11=A10+1
A12=A11+1
E15=F13-E13

<tbody>
</tbody>

<tbody>
</tbody>

Replies
6
Views
209
Replies
2
Views
356
Replies
14
Views
365
Replies
4
Views
466
Replies
10
Views
480

### Forum statistics

1,196,010
Messages
6,012,843
Members
441,733
Latest member
MartijnB ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back