CODEWITHSUNDEEP
excelexcel-formula
wheel58m
wheel58m

Reputation: 124

Excel: Calculate a cell based on values below matching criteria from another column

So, I have a list of letters and numbers, each letter has a number assigned to it. In a separate column, I have one instance of a letter - I'll refer to these rows as parent rows. Each parent row has an empty value in the number column. I'd like to be able to calculate the mode of the number values beneath the parent row of a given letter and populate the parent row number with that. (See Screenshot). I'd be doing this for multiple parent rows - I'd like to be able to filter the number row by blank values and fill the formula down. Is this even possible?

I've tried using =MODE(IF("LetterColumn"="Letter","Numbers")

Example Spreadsheet

Mode for "F" would be 10. Mode for "G" Would be 9.

Upvotes: 0

Views: 472

Answers (2)

P.b
P.b

Reputation: 11415

=MODE.MULT(C3:INDEX(C:C,MAX((B:B=A2)*(ROW(B:B))))) not sure if this is what you mean, since you don't show the desired result, but this uses MODE(.MULT) over the range from the cell below untill the last cell where the letter equals. This is provided that the range shows no gaps as in the example. Like mentioned offset recalculates each time a cell is changed. INDEX doesn't, so it's only recalculating if a range is changed effecting the formula, unlike OFFSET. enter image description here

Upvotes: 1

Qualia Communications
Qualia Communications

Reputation: 715

I used the OFFSET function to avoid a circular reference.

Formula in C2: =MODE(OFFSET(C2,1,0,COUNTIF(B:B,B2)-1,1))

Result:

enter image description here

Notes:

  • In order to work, values in B need to be in consecutive order.
  • Heavy use of OFFSET formula can slow down your Excel

Let me know, if you need an explanation.

Upvotes: 0

Related Questions