0

I have basic to medium knowledge in Excel, and I am having trouble with a somewhat complicated problem for me.

I have a table where I need to relate three columns: a "Type" column, and another two with numeric values, which I need to divide by one.

The problem is: I would like to perform this calculation only for rows that have a certain "Type", and then average the results. Something like the image linked below.

I know more or less how logic should work. I tried using the "Format as Table" function and using the "IF" function with the column name, something like IF (Sheet1 [Type] = "A"; Sheet1 [Data 2] / Sheet1 [Data 1]; "NA "), but I think that's not how it works.

I also tried the VLOOKUP function, but I couldn't think straight out how I could average the values later.

Thank you for your help.

The result would be something like this, but for a very large table it would be impracticable to do so. Example of the problem

2
  • Please include your expected result as per your sample data. Also include your own attempt at least.
    – JvdV
    Oct 4, 2019 at 13:50
  • Thanks for the edited answer: Without any helper column: =SUMPRODUCT((A$2:A$12=E2)*(C$2:C$12/B$2:B$12))/COUNTIF(A$2:A$12;E2) should do it.
    – JvdV
    Oct 4, 2019 at 15:04

1 Answer 1

1

I guess you could create an auxiliary column with Data 2/Data 1, then use this column to calculate the average using something like:

=SUMIF(A2:A100,"A",D2:D100)/COUNTIF(A2:A100,"A")

Where

  • Column A is the Type (Contains "A" or "B")
  • Column B is Data 1
  • Column C is Data 2
  • Column D is the auxiliary column (Data 2/Data 1)

Example Screenshot

I don't think you can go straight with a formula to the result without the auxiliary calculated column.

I hope it helps.


As suggested by máté-juhász, AVERAGEIF is a much better way:

=AVERAGEIF(A2:A100,"A",D2:D100)
2
  • 1
    Why not use AVERAGEIF? Oct 4, 2019 at 14:27
  • OK. I was thinking in a much more complicated way than I needed. Using the AVERAGEIF function like this was exactly what I couldn't think of. Thank you for helping!
    – Lucas
    Oct 4, 2019 at 14:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .