Forum Discussion
Find average with checkboxes
AVERAGEIF or AVERAGEIFS are probably what you want but you need to give it ranges. so assuming you want to average all the cell from C11 to G11 if the checkbox up and to the right of it is checked then:
=AVERAGEIF(D10:H10,TRUE,C11:G11)
If you have additional criteria or need to limit it further you can use AVERAGEIFS (but note the order of the arguments changes):
=AVERAGEIFS(C11:G11,D10:H10,TRUE)
If you can't use the ranges to define the average range and the conditional range(s) then you may need to manually do the averaging:
=SUM(C11*D10,G11*H10)/SUM(--D10,--H10)
in this case we take advantage of TRUE=>1 and FALSE=>0 when converted to a value and that excel will convert them to values when used in an equation (i.e. multiplied in the first SUM and the "--" which is negative of a negative in the second SUM. the second sum could also use "0+" or "1*" to convert the T/F to 1/0)
Thank you! This did exactly what I needed by using the SUM function with 1 exception. Some of the cells are blank so I get an #VALUE! Error. If I ignore those cells in my equation it works, but I would rather it ignore the blank cells. I have tried putting IGNOREIF in there but with no luck. The blank cells have an #DIV/0! and I use IFERROR to correct it but now I see it just hides it.