Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community

Average of a selected range

Copper Contributor

Hello,

 

I am trying to average a selected range but have the function ignore any cells that have "N/A" in them. For example, I would like to average cells M2 - AQ2 in my worksheet. The numbers range from 0-3 but N/A is a possibility as well. This is a live worksheet that is populated from a Google Form. I keep getting the #DIV/0! error when I try to create the average. The equation I am using is =AVERAGE (M2:AQ2).2023-12-08_14-56-26.png

 

Is this possible?

2 Replies

@bmorley 

Perhaps this one:

=AGGREGATE(1,6,M2:AQ2)

@bmorley 

=AVERAGE(TOROW(M2:AQ2,2))

 

With the help of TOROW you can ignore errors.

average.png