CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Falcon4ch
Falcon4ch

Reputation: 120

Can you average a column of values ignoring duplicates from another column?

I have a sheet that looks similar to this:

enter image description here

I'd like to have a formula in cell B1 that averages all values from B2:B, but ignores duplicate values in column A and their corresponding values in column B.

Right now an average of column B in the sheet above gives me 4.7777. Ideally it would give me an average of 5.

Appreciate any help you could provide!

Link to sheet - https://docs.google.com/spreadsheets/d/1cuibKscWfUMzaPxi6J1bWxr8YKlcl9rEGKjL3gf-V5k/edit?usp=sharing

Upvotes: 0

Views: 230

Answers (1)

Aresvik
Aresvik

Reputation: 4620

Try:

=average(filter(B2:B,countifs(A2:A,A2:A,row(A2:A),"<="&row(A2:A))=1))

Upvotes: 2

Related Questions