0

Basically, I have a table with a bunch of people names vertically and months listed horizontally. Then I have a drop-down list of all months. For the purposes of =AVERAGEIF I want to use that drop-down list to point to a corresponding column without making 12 different named ranges for every page with the same table I'll need to make in the future.

An example:
I want something like =AVERAGEIF(name_range; Greta; March) when March is selected in a drop-down, which will return an average between $2557 and $3367.

example table

Improve this question
3
  • Hi and welcome to WebApps. Would you explain how the value = Greta is determined if there is only one set dropdowns (Month)? Are you sure that you're not assuming a dropdown for "Names" as well?
    – Tedinoz
    Jan 7 at 1:02
  • Hello, @Tedinoz I will be using requested formula on another sheet with a table of names made with =unique(name_range).
    – Vladimir K.
    Jan 7 at 19:06
  • Hi. OK - 2 things: 1) =unique(name_range) will only deliver a list on names, it won't enable you to select one. That's why I think you need the dropdown (or perhaps that is what you intended). FWIW, a dropdown will generate a list of unique values, so you wouldn't need to use UNIQUE. 2) For future reference, your explanation: I will be using requested formula on another sheet with a table of names made with =unique(name_range) is pretty important and is the type of thing that should be included in the body of the question.
    – Tedinoz
    Jan 8 at 0:26

1 Answer 1

Reset to default
0

There are likely to be many ways to answer this question. However you have specifically cited AVERAGEIF.

There is an existing dropdown to identify the Month.

  • There needs to be an additional dropdown to identify the Name.

  • With those pieces of data, try this formula =averageif(A2:A6;H2;indirect(address(2;month($I$2&1)+1;4)&":"&address(6;month($I$2&1)+1;4)))

  • Or for those in a locale that uses a decimal point =averageif(A2:A6,H2,indirect(address(2,month($I$2&1)+1,4)&":"&address(6,month($I$2&1)+1,4)))

Sample

snapshot

Logic

  • criteria range = A2:A6 but you can sustitute your named range`
  • criterion = value of the "Name" dropdown
  • [average range] = supplied by INDIRECT
    • range based on address function
      • Row: Start:2, End:6
      • Column: month($I$2&1)+1 this converts the string value of the Month dropdown into a number. Add 1 because the function is zero-based.
Improve this answer
2
  • It works! Yeah, I was finally resigned to using named ranges. But then I had understood why this formula didn't work for the first time - my locale. In my locale, a semicolon ; is a divider, so all formulae using a simple comma , will refuse to work.
    – Vladimir K.
    Jan 7 at 19:04
  • My bad. I did notice the locale aspect of your formula. Anyway, I've edited the answer to include added both versions.
    – Tedinoz
    Jan 8 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.