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.
<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.
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.