Average IF Arrays with multiple criteria in two columns: Please help me crack it!

[excellearner18]

Hi,

I have got so much help from this forum in the past, so I am hoping that you can help me with my specific query, before it drives me mad:

I am working with a survey and want to be able to work out average answers by groups of job titles (front of house, back of house etc),-I have been using the below: EG; Rate how much you enjoy your job is going on a scale of 1-5:

So for grouping I did: =AVERAGE(IF(ISNUMBER(MATCH('Raw Data'!C4:C80, {"FOH1","FOH2","FOH3","Bar","Clean","Wait","sweep","glassc"},0)), 'Raw Data'!E4:E80))

With C4:C80, being the column at the beginning of the survey where they listed their job titles, and E4:E80 being where they answered 1-5 for the question.
HOWEVER, I realised that if someone answers one of the job titles listed, but declines to answer this question, then the average is skewed, as this sum still divides the total from column E, by whatever the number of {"FOH1","FOH2","FOH3","Bar","Clean","Wait","sweep","glassc"}s is.

So I want to amend it to say that they meet the above job title criteria in C4:C80, AND they put numbers 1-5 (or the answer is not blank) in E4:E80, then find the average answer. Is this possible? I have tried all the ways I can think of, and cannot seem to crack it!

Any help would be greatly appreciated. I should have done pivot tables, however, this is a large survey and I have come this far with arrays, I would need to redo the whole thing, and the logical part of my mind knows there must be a simple solution! Thanks so much!

 

[pgc01]

Hi

Test if the value in E4:E80 is a number value, like;

=AVERAGE(IF(ISNUMBER(MATCH('Raw Data'!C4:C80,{"FOH1","FOH2","FOH3","Bar","Clean","Wait","sweep","glassc"},0)),IF(ISNUMBER('Raw Data'!E4:E80),'Raw Data'!E4:E80)))

... confirmed with CSE

 

[excellearner18]

Thank you that has done the trick! I am so grateful that you took the time to share the simple solution, which was beyond me after days of staring at the sheet!!

THANKS AGAIN!

 

[pgc01]

You're welcome. Thanks for the feedback.