Search code examples
excelaverageformulaoffsetcountif

EXCEL calculating average with OFFSET (coord unspecified) of a given keyword


Fun situation I'm trying to calculate. Basically in one row I have names of products, and the row to the right of it the number of days that have passed since the product was first received.

The calculation to do the days for ex is

=TODAY()-BB2

What I'm trying to do NOW, is identify let's say the product word "truck," and then calculate how many days on average the holding time of truck is.

I understand the logic of the formula I need, just not how to execute precisely. Basically the formula is going to need to use this average calculator with a keyword identified COUNTIF

=COUNTIF($A$2:$A$900,"TRUCK")/COUNTA($A$2:$A$900)

What I'm missing is some type of...IF "TRUCK, OFFSET (GIVEN CELL) -1)

Thanks for any thoughts! -Wilson


Solution

  • A formula (in C1 in the example):

    =AVERAGEIF(A:A,"truck",B:B)
    

    should work but I would recommend a PivotTable for the additional functionality it provides:

    SO44575493 example