Average Cell Formula Help

[APC8915]

Hey guys, I'm having difficulty trying to get an average result using multiple named ranges, so was wondering if anyone can help me with this formula. I don't use Excel very often for this sort of thing, so don't know if it's possible, if I'm making it too complicated, or if I'm just missing something. I have attached screenshots to try and make it easier to explain.

The average result I'm trying to achieve is in cell Q34, the results for "Adam". Effectively, it is data taken from "Intro HSI" and "Experience HSI" that I am looking for the average from. The formula works and provides the average as required when data is input in to cells E4, E6, E21 and E23.

However, if there is no data in E4 and E6, only in E21 and E23, cell Q34 returns "#DIV/0!". Likewise, if there is no data in E21 and E23, only in E4 and E6, cell Q34 returns "#DIV/0!".

I need cell Q34 to be able to give me an average if the date in column E is complete for both "Intro" and "Experience", or if only one or the other has data present. The "Leader" in column C is variable, and could appear in multiple rows, so the formula can't be based on the cells in column C, they are in a named range.

If anyone can help with the formula, or knows a better way to go about this, I would really appreciate it.

Cheers

Screenshot (1).png
Screenshot (2).png
Screenshot (3).png

[XLent]

I may have misunderstood but perhaps the below would for you?

=AVERAGE(CHOOSE({1,2},IFERROR(AVERAGEIF(intro_leaders,leaders,intro_support),FALSE),IFERROR(AVERAGEIF(experience_leader,leaders,experience_support),FALSE)))

the above would return the AVERAGE of the AVERAGEIFS, and ignore either/or AVERAGEIF if #DIV/0!
if both AVERAGEIFs were returning #DIV/0! then #DIV/0! would be returned by the outer AVERAGE.

[Pepe Le Mokko]

@APCB
in the future please post a sample sheet. Pics are nice to look at, but useless to work with (click Go advanced - Manage attachments)
Thanks

[APC8915]

XLent - That worked a treat, thanks so much for that!

Pepe - That makes sense, didn't even realise that was an option, I will for future.

Thanks guys.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.