0

I'm an absolute novice and have created a Google Sheets for cooking recipes and am trying to create arrays so every time I add a recipe form a new book/chef/country, their score automatically updates.

Arrays are working fine for all formulas except AVERAGEIF and AVERAGEIFS.

This is the document: https://docs.google.com/spreadsheets/d/1r3bpNFy9k1h8anZJfefk6KrGYSy7mW2izxKQZb9mWoU/edit?usp=sharing

The particular problem points are in the hidden tabs (Book Rating, Chef Rating, Country Rating) in Columns F, H and I.

ajw136
  • 27
  • 3
  • Did you fix this? Please try to keep your questions more focussed - see [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). – iansedano Dec 14 '20 at 15:33

1 Answers1

1

try:

=ARRAYFORMULA(IF(B2:B<3, "-", IFNA(VLOOKUP(A2:A, QUERY(
 {'Form Responses 1'!C:C,'Form Responses 1'!L:L}, 
 "select Col1,avg(Col2) group by Col1"), 2, 0))))

enter image description here

player0
  • 69,261
  • 8
  • 33
  • 67
  • 1
    This has worked for those columns - thanks @player0. How do I use the same logic to add an array to an AVERAGIFS which includes a wildcard search? (Columns H and I) – ajw136 Dec 13 '20 at 21:34
  • @ajw136 https://i.stack.imgur.com/R4Ckh.png – player0 Dec 13 '20 at 21:39
  • Thanks @player0. I'm almost there I think - much appreciated. The only issue - when I apply this to the Country Rating tab, I can't get it to pick up those with a Veg/Vegan count of 3 or more. – ajw136 Dec 13 '20 at 22:18