Search code examples
excelexcel-formulaexcel-2007

excel find nearest cell that meets a criteria

I'm trying to write a formula to find the nearest cell that meets a certain criteria in short i have a table of data for an antenna that denotes its performance at various angles around it, and I want to calculate the 3dB beamwidth. the table is in the form:

+-------------------------------------+
| Angle    | Freq 1 | Freq 2 | Freq 3 |
+----------+--------+--------+--------+
| 0        | -2     | -4     | -6     |
| 10       | -2.5   | 4      | -7     |
| 20       | -2     | 5      | 0      |
| 30       | 1      | 6      | 2      |
| 40       | 4      | 7      | 2      |
| 50       | 5      | 6      | 3      |
| 60       | 4      | 6      | 2      |
| 70       | 2      | 5      | 2      |
| 80       | 0      | 4      | 2      |
| 90       | -2.5   | 2      | 1      |
| 100      | -2     | -4     | 0      |
| ...      | ...    | ...    | ...    |
| 350      | -2     | -4     | -6     |
| 360      | -2     | -4     | -6     |
+----------+--------+--------+--------+
| Max      | 5      | 7      | 3      |
| Ang. Max | 50     | 40     | 50     |
+----------+--------+--------+--------+

so i currently use max to get the highest value in the table, and INDEX([angle range],MATCH([max cell],[freq column range],0)) to look up the corresponding angle of that maximum value.

I need a formula that will find the corresponding angle for the last cell above the maximum that is withing 3dB of the max and the angle for the last cell below it, then get the difference between those angles to get the beamwidth in degrees, so for Freq 1:
above: 4 - angle 40
below: 2 - angle 70
beamwidth: 70 - 40 = 30

freq 2:
above: 4 - angle 10
below: 4 - angle 80
beamwidth: 80 - 10 = 70

freq 3:
above: 0 - angle 20
below: 0 - angle 100
beamwidth: 100 - 20 = 80

ideally it would work linearly interpolate between the last cell that is withing 3dB and the first outside to guess the angle when between these steps... but that might be a bridge too far.

I want to avoid doing this with a macro because I want it to be calculated automatically any time the source data changes, and i need to be able to send this around to machines that are not allowed to run macros.

I thought about using LOOKUP of VLOOKUP but these need to match an exact value unless an option is set, but when i tried with that option set I got no matches at all for the value of (max - 3)

not really sure what else to try.

Solution

  • For Excel 2010 and above:

    To find the Above:

    =INDEX($A:$A,AGGREGATE(15,6,ROW(B$2:B$12)/(B$2:B$12>=MAX(B$2:B$12)-3),1))

    To find the Below, change the 15 to 14:

    =INDEX($A:$A,AGGREGATE(14,6,ROW(B$2:B$12)/(B$2:B$12>=MAX(B$2:B$12)-3),1))

    Then drag/copy across to reference the next columns.

    enter image description here

    For 2007 and below you will need to use the following array formulas:

    For Above:

    =INDEX($A:$A,SMALL(IF(B$2:B$12>=MAX(B$2:B$12)-3,ROW(B$2:B$12)),1))

    For Below:

    =INDEX($A:$A,LARGE(IF(B$2:B$12>=MAX(B$2:B$12)-3,ROW(B$2:B$12)),1))

    Being Array formulas they need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

    enter image description here