Hi and thanks in advance in case anyone can help me find a solution. I am trying to combine an index match with an average if. Attached is a screenshot, and the green cells are supposed to calculate the average P/E ratios depending on the timeframe in B4-D4 (1yr-5yr). So for the 5yr (green cell D5) I calculate the average of G5-ZZ5. For 2yr (C5) I calculate the average backwards from 19 Oct 2018 to 25 Oct 2016 with the following formula:
={AVERAGE(IF(G3:ZZ3>=TODAY()-(2*364),G4:ZZ4,"0"))}
--> Calculate the average if the date is between today and two years back
This formula seems to work more or less, but what I am really trying to solve is to combine this formula with an Index-Match or Vlookup, depending on what fits better. There is a dropdown menu in A5 where I can choose between Company 1, Company 2, and Company 3, and I need the cells from B5-D5 to change automatically depending on the company.
Any ideas how to combine the functions?
={AVERAGE(IF(G3:ZZ3>=TODAY()-(2*364),G4:ZZ4,"0"))}
--> Calculate the average if the date is between today and two years back
This formula seems to work more or less, but what I am really trying to solve is to combine this formula with an Index-Match or Vlookup, depending on what fits better. There is a dropdown menu in A5 where I can choose between Company 1, Company 2, and Company 3, and I need the cells from B5-D5 to change automatically depending on the company.
Any ideas how to combine the functions?
