Home

What's wrong with my Average IF formula?

jordan.lombard
New Contributor

hi everyone,


I wonder if you can help me with a question I have;


I want to use the word in Column A to search another sheet - every time it finds a particular word in a sentence (column B) it uses the OR figure in the cells next to the sentence (column C) and calculates an average for those figures.


I ended up with "=AVERAGEIF($A$3:$A$20812,A3,Subject_Input!$C$400:$C$427)" 


This works for the first 20 or so cells but after that, it stops working even though I know the figures are there.


Does anybody have any suggestions on what my problem could be after I get past the 20th cell?

EDIT:

Here's a copy of the sheet for reference: https://drive.google.com/file/d/1EXS...ew?usp=sharing


I'll reference the sheet as I explain it a little better;



  • On the sheet titled 'weighting', you'll see over 20,000 individual words, emojis, etc in column A.
  • In column D of the same sheet, you'll see percentage figures titled 'OR'.
  • On the sheet titled 'subject_input', you'll notice next to each 'full subject' (column B) there are OR% figures.




What I want to do is find the average OR% figure for each word if that makes sense?


For example;
If we're using the word 'travel in cell A10 of 'weighting'.


Every time the word travel appears in the full subject (column B) 'subject_input' then the OR figure next to it would be totalled up and averaged to produce an overall average for those particular words.


To try and give a simpler example I've created this sheet; https://drive.google.com/file/d/1gQE...ew?usp=sharing


am I making much sense here?

If anyone can help me out here I'd really appreciate it!

1 Reply

SOLVED: FOR THOSE LOOKING FOR A SIMILAR SOLUTION THIS IS WHAT I HAVE' =AVERAGEIF(SHEETNAME!RANGE:RANGE,"*"&REFERENCE CELL&"*",SHEETNAME!RANGE:RANGE)