Highlighted
New Contributor

# What's wrong with my Average IF formula?

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!