Forum Discussion
Find average with checkboxes
Hello,
I’m not fluent in Excel but I do alright. Currently I am having a real hard time figuring this out. I’m trying to pull averages from different cells to one cell but based on if a box is checked.
Backstory: I track times in a task and get the averages for each person but also the store average. As some people are no longer in those roles or now doing part time, I want the store average to only show the data from the people I choose.
Ex: cell I3 is the store average. I only want it to account for the data in cell C11 if the checkbox in D10 is checked. I only want it to account for the data in cell G11 if the checkbox in cell H10 is checked. And so on and so forth.
I have tried:
=IF(D10,C11,IF(H10,G11))
=AVERAGEIF(D10,”TRUE”,C11(H10,”TRUE”,G11))
=AVERAGEIF(D10,”TRUE”’C11)&AVERAGEIF(H10’TRUE”,G11)
but none of those have worked.
1 Reply
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)
