Iterative Calculations and Match issues with out of range values.

RainGauge

New Member
Joined
Jul 15, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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:
MATCH(ADDRESS(ROW(OFFSET(INDIRECT($G2),0,-1)),COLUMN(OFFSET(INDIRECT($G2),-1,0))),$G$2:$G$13,0))

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
    Iterative Correction.png
    17.4 KB · Views: 16

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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)
 
Upvote 0
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top