CODEWITHSUNDEEP
excelrowscalculation
potatoes
potatoes

Reputation: 11

Calculations at the bottom of each column

I am using an excel spreadsheet to keep track of some data.

enter image description here

I calculate median and quartile values for each column at the bottom of those columns (rows 15, 16, 17). However, I want to be able to keep adding new values into columns without changing the range of my median/quartile formula. I know I can select the whole column in my formula if I had the formula in a different cell than the column I am making calculations on.

I am wondering if there is a way to exclude the rows with my median and quartile formulas from my calculations. As I enter more data, those locations will also increase and I couldn't figure it out.

Upvotes: 1

Views: 45

Answers (1)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

So if we know the number of blank lines to be two, before we do the median, then =median(INDIRECT(CONCATENATE("B2:B",text(row()-3,"#")))) works, by constructing the range of column B fed to the median function to be B2 through B + whatever number is 3 up from the row where the formula sits. For the IQR presumably 3 must be increased.

Upvotes: 1

Related Questions