Forum Discussion
Using AveragerIFS with multiple criteria
Using AveragerIFS with multiple criteria that would create 2 sets of data. This data needs to have a weighted average applied to it. I am using this formula (=AVERAGEIFS(('Main Daily Data Updated'!I:I),('Main Daily Data Updated'!$A:$A),">="&$B27,('Main Daily Data Updated'!$A:$A),"<="&$C27,('Main Daily Data Updated'!$B:$B),"<"&275) and this =AVERAGEIFS(('Main Daily Data Updated'!I:I),('Main Daily Data Updated'!$A:$A),">="&$B26,('Main Daily Data Updated'!$A:$A),"<="&$C26,('Main Daily Data Updated'!$B:$B),"<"&320) and then the weighted average =(($D26*F26)+($D27*F27))/$D28) to get each half of the data and calculating the weighted average from there. Is there a way to put this all into one formula?
4 Replies
- Hello,
The AVERAGEIFS is just like SUMIFS.. It has the following arguments to supply
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)
The way you used the AVERAGEIFS function isn't the way to use it..
You can check the Office link below to understand how best to use it based on your dataset
https://support.office.com/en-us/article/averageifs-function-48910c45-1fc0-4389-a028-f7c5c3001690I have used the AverageIFS to get the average of a data set within certain parameters and with set criteria. I am trying to add to that since the criteria has change mid stream. So, instead of using 2 formulas and use weighted average. I am trying to combine them into one formula. Not sure if it can be done, but I am looking to see if anyone has done anything like this.
skabilly I'm assuming the weighted average formula =(($D26*F26)+($D27*F27))/$D28) refers to the cells where the averageifs formulas are located ($D26 and $D27) and cells with corresponding countifs (F26 and F27) to create proper weighted average. if that is the case you could create a single large formula by replacing those cell refs with the actual formula you listed earlier. If instead you are trying to have a single averageifs with OR condition inside, I don't think that will work. But you could simplify your formulas using sumifs and then divide by the that total ($D28) instead of averageifs and then multiply by the count since that is just going back to the sum anyway.
Hello! You've posted your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space (which is what I am assuming your question is regarding) - please post Excel questions here in the future.
