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.
3 Replies
Hello KelseyErwinMusic,
If your checkboxes are linked to cells that return TRUE/FALSE, you can average only the selected values with this formula:=IF(COUNTIF({D10,H10},TRUE)=0, "", SUMPRODUCT({C11,G11}*--{D10,H10}) / SUMPRODUCT(--{D10,H10}) )- Works by multiplying each value with its checkbox (TRUE = 1, FALSE = 0).
- Divides the sum of checked values by the number of checked boxes.
- Returns blank if no boxes are checked.
Extend the arrays {C11,G11} and {D10,H10} for more people, or use a column setup for a simpler formula:
=IF(COUNTIF(H10:H20,TRUE)=0, "", SUMPRODUCT(C11:C21*--H10:H20) / SUMPRODUCT(--H10:H20) )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.
