All,
I've scoured the internet looking for an answer to this problem, but am left unsatisfied. Maybe i'm over complicating things, & I hope someone can help me out.
I currently have a spreadsheet that is updated on a monthly basis where I need to calculate the median of the last 7 reportable (i.e. non-zero) values prior to my current data. I cannot simply "remove blanks" as I have additional pertinent information on adjacent columns. Here's a sample dataset:
<tbody>
</tbody>
Assuming 4/1/17 is my current dataset, I need to calculate the median of the last 7 data points in column B prior to 4/1/17 (i.e. last 7 values from 1/1/16 through 3/1/17 [rows 1 through 15]) to compare to my current data. I am using the array formula:
=MEDIAN(INDIRECT("B"&LARGE(ISNUMBER(B1:B15)*ROW(B1:B15),7)&":B15"))
which ultimately calculates the correct result, but that array formula is cumbersome. I also have thousands of these discrete datasets where the "B" in INDIRECT("B" and the ":B15") at the end of the formula don't carry when I copy from row-to-row and column-to-column. Is there a more elegant (preferably non-array) formula I can use that I haven't thought of?
As an added bonus, is there any way I could make the formula automatically update as I insert data for 5/1/17 and beyond (i.e. insert data into row 17)?
Thank you for all of the help I've gotten over the years from the experts at Mr.Excel.
I've scoured the internet looking for an answer to this problem, but am left unsatisfied. Maybe i'm over complicating things, & I hope someone can help me out.
I currently have a spreadsheet that is updated on a monthly basis where I need to calculate the median of the last 7 reportable (i.e. non-zero) values prior to my current data. I cannot simply "remove blanks" as I have additional pertinent information on adjacent columns. Here's a sample dataset:
| A | B | C | |
| 1 | 1/1/16 | 1 | 0.2 |
| 2 | 2/1/16 | 5 | 3 |
| 3 | 3/1/16 | 15 | 6 |
| 4 | 4/1/16 | 9 | |
| 5 | 5/1/16 | 15 | |
| 6 | 6/1/16 | 4 | 24 |
| 7 | 7/1/16 | 5 | 17 |
| 8 | 8/1/16 | 10 | |
| 9 | 9/1/16 | 2 | |
| 10 | 10/1/16 | 3 | |
| 11 | 11/1/16 | 15 | |
| 12 | 12/1/16 | 0.5 | 56 |
| 13 | 1/1/17 | 54 | |
| 14 | 2/1/17 | 11 | 3 |
| 15 | 3/1/17 | 13 | 9 |
| 16 | 4/1/17 | 17 | 1 |
| 17 | Median of last 7 value | FORMULA? | 9 |
<tbody>
</tbody>
Assuming 4/1/17 is my current dataset, I need to calculate the median of the last 7 data points in column B prior to 4/1/17 (i.e. last 7 values from 1/1/16 through 3/1/17 [rows 1 through 15]) to compare to my current data. I am using the array formula:
=MEDIAN(INDIRECT("B"&LARGE(ISNUMBER(B1:B15)*ROW(B1:B15),7)&":B15"))
which ultimately calculates the correct result, but that array formula is cumbersome. I also have thousands of these discrete datasets where the "B" in INDIRECT("B" and the ":B15") at the end of the formula don't carry when I copy from row-to-row and column-to-column. Is there a more elegant (preferably non-array) formula I can use that I haven't thought of?
As an added bonus, is there any way I could make the formula automatically update as I insert data for 5/1/17 and beyond (i.e. insert data into row 17)?
Thank you for all of the help I've gotten over the years from the experts at Mr.Excel.
