# Iterative Calculations and Match issues with out of range values.

#### RainGauge

##### New Member
I've hit a wall and am not certain how to proceed. Any help is appreciated!

The attached image is a representation of my worksheet which is much larger.

Problem: Column I & J & K is to be populated with an equation that iteratively calculates the median of its neighboring cells. Column I uses H, while J uses I and K uses J.

Column G and H are paired datasets that represent information pulled from a table, G being the cell reference and H being its value.

The goal of the equation is to have it take the cell reference in column G and then find the neighboring cells of its INDIRECT reference, ex: D4 would calculate the median of C3,C4,C5,D3,D5 (and E3,E4,E5 assuming they weren't off the table).

This is what I've come up with:
=MEDIAN(INDEX(H\$2:H\$13,TARGET CELL,TARGET CELL2,TARGET CELL3,TARGET CELL4,TARGET CELL5,TARGET CELL6,TARGET CELL7,TARGET CELL8)

Each TARGET CELL has a variation of the following, where the offset is modified to target a different direction:

It gives me an #N/A error when MATCH attempts to return a value that has no match in column G (It's value is at the edge of the table).
Is there a way to have my equation calculate the median while discarding the match results that return #N/A?

I was hoping to have this all calculated in single cells, but if it isn't possible I am aware that I can calculate each neighboring cell in its own helper column then discard the errors.
Is there a better approach or a solution that doesn't involve 8 helper columns? Thanks in advance for taking the time to read my question.

#### Attachments

• Iterative Correction.png
17.4 KB · Views: 14

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

I think this what you're describing, but I don't follow what you're doing in the iterations:

ABCDEFGHIJK
1SampleSample
2
32.03.03.12.03.03.1
41.05.02.01.05.02.0
53.01.03.03.01.03.0
64.01.11.04.01.11.0
7
8MedianMedian
93.02.03.03.02.03.0
103.02.53.03.02.53.0
111.12.51.61.12.51.1
121.13.01.11.13.01.1
13
143.01.0
151.05.0
165.02.0
173.03.0
183.0
194.0
201.1
211.0
222.5
23
Sheet1
Cell Formulas
RangeFormula
B9:C9,H9:I9B9=MEDIAN(A1:C1,A4:C4,A3,C3)
D9,J9D9=MEDIAN(C1:J1,C4:J4,C3,J3)
B10:C11,H10:I11B10=MEDIAN(A3:C3,A5:C5,A4,C4)
D10:D11,J10:J11D10=MEDIAN(C3:J3,C5:J5,C4,J4)
B12:C12,H12:I12B12=MEDIAN(A5:C5,A8:C8,A6,C6)
D12,J12D12=MEDIAN(C5:J5,C8:J8,C6,J6)
B14B14=C3
B15,I15B15=B4
B16,I16B16=C4
B17B17=MEDIAN(B14:B16)
I14I14=H4
I17I17=H5
I18I18=J5
I19I19=H6
I20I20=I6
I21I21=J6
I22I22=MEDIAN(I14:I21)

#### RainGauge

##### New Member
Thanks for the response.

I realize I could have been more clear about what was the purpose of the equation and what I was trying to achieve. In case you're curious, I was working with a large dataset of weather radar data that contained obviously erroneous data. The goal of the exercise was to approximate the value of cells that contained these errors using its surrounding data. The multiple passes (the iteration) was to stabilize the data in case an erroneous cell was adjacent to another erroneous cell.

I ended up creating multiple iterations of the entire table each referring to its previous incarnation. Regardless, I found a solution and the problem is solved.

Replies
3
Views
182
Replies
13
Views
315
Replies
0
Views
111
Replies
0
Views
202
Replies
0
Views
37

1,181,609
Messages
5,930,903
Members
436,766
Latest member
azex85

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