What's wrong with my Average IF formula?

[Jlombard_]

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/1EXSv1YuB7T9oIgmV2BUESW7yS1rN4Vbi/view?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/1gQEsFqkDHfIvwdNxOVmfu2U6bU_m8WFM/view?usp=sharing


am I making much sense here?
[h=2]What's wrong with my Average IF formula?[/h]

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

 

[RoryA]

Your criteria range and value range are different sizes, so that won't work (and doesn't really make sense).

 

[Jlombard_]

Your criteria range and value range are different sizes, so that won't work (and doesn't really make sense).

Do you have a suggestion to achieve the intended outcome as I've explained above? - The formula I've used was recommended to me on reddit and I simply tweaked it a little but like you said it doesn't appear to make much sense.

 

[RoryA]

It looks to me like you want:

=AVERAGEIF(Subject_input!B:B,"*"&A2&"*",Subject_input!C:C)

 

[Jlombard_]

It looks to me like you want:

=AVERAGEIF(Subject_input!B:B,"*"&A2&"*",Subject_input!C:C)

you sir are my hero!

 

[RoryA]

Glad to help.

 

[Jlombard_]

Glad to help.

I wonder if there's one more VBA related question you could help with?


I'm working in a search spreadsheet which has a whole host of individual weighted words.
​
When a user types in a particular sentence into the (Sheet: tester) "Search_box1" field, upon clicking a button I want each word in "search_box1" to be referenced in a separate sheet called "weighting" (Column A3:A) - in column H there is a score for each word.
​
When the words have been found in the system I want them to be added together to form an overall grade of the sentence which will be displayed in a sheet called 'tester' (CELL REF: GRADE_VALUE).
​
At the same time, once the button has been clicked I'd want a copy of both the GRADE_VALUE and the Search_box1 cell logged into a sheet called 'history_search'. cells A2 onwards (GRADE_VALUE) and B2 onwards (Search_box1).
​
The problem here is I really don't know where to start with coding the button as this is a little out of my depth to say the least!