Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

problems with averageifs formula

hi Folks
I've created an averagesifs function but it keeps coming up with Div/o. I've attached an example. It's on the Readiness to train sheet. Essentially I want the formula to match the person and the week number on the list (that bit works fine) and then for each of the categories add up the score for each heading e.g. Mood/motivation etc. The numbers are formatted as general. But I can't see why it's not giving me an average score based on that person, week number and each of the symptoms..thanks as always.
EE-monitoring-average-if-formula.xlsx
0
agwalsh
Asked:
agwalsh
  • 4
  • 3
3 Solutions
 
Rob HensonIT & Database AssistantCommented:
You have two criteria referring to the Symptons sheet.

All of the Criteria within the IFS functions are dealt with as AND operators, so the result of the criteria for "Mood/Motivation" AND "Energy Levels" is zero.

I suspect you can use a SUMPRODUCT instead but wouldn't like to say for definite.

Alternative would be to use a combination of SUMIFS and COUNTIFS:

Without going into the full formula, it would be along the lines of:

=(SUMIFS(Criteria1)+SUMIFS(Criteria2)) / (COUNTIFS(Criteria1)+COUNTIFS(Criteria2))

The result for your Conor example would be:
SUMIFS
Mood - (4 + 1) = 5
Energy - (3 + 2) = 5
Total 10

 COUNTIFS
Mood - 2
Energy - 2
Total 4

SUMIFS / COUNTIFS = 2.5

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
Full formula was easy enough to replicate in the end:

=(SUMIFS(Table1[Score],Table1[Name],$A2,Table1[Week number],B$1,Table1[Symptoms],Sheet6!$A$1)+SUMIFS(Table1[Score],Table1[Name],$A2,Table1[Week number],B$1,Table1[Symptoms],Sheet6!$A$2))/(COUNTIFS(Table1[Name],$A2,Table1[Week number],B$1,Table1[Symptoms],Sheet6!$A$1)+COUNTIFS(Table1[Name],$A2,Table1[Week number],B$1,Table1[Symptoms],Sheet6!$A$2))

Note: SUMIFS has SUM range as first parameter; COUNTIFS has no such parameter, just criteria.

Thanks
Rob H
0
 
Rory ArchibaldCommented:
You could also array enter:

=SUM(SUMIFS(Table1[Score],Table1[Name],'Readiness to train data'!$A2,Table1[Week number],'Readiness to train data'!B$1,Table1[Symptoms],Sheet6!$A$1:$A$2))/SUM(COUNTIFS(Table1[Name],'Readiness to train data'!$A2,Table1[Week number],'Readiness to train data'!B$1,Table1[Symptoms],Sheet6!$A$1:$A$2))
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
agwalshAuthor Commented:
hm, just wondering why the averageifs didn't work though...but I might just try the averageifs and array enter it....and see if that works..
0
 
agwalshAuthor Commented:
Yep, people that worked (player data) ..although what I want to do now is add a formula that will do that calculation but only for the week number and player specified in the Readiness to train sheet. Thanks :-)
monitoring-average-if-formula-vers-02.xl
0
 
Rob HensonIT & Database AssistantCommented:
Do you not want the Symptoms part of it then?

Without the symptoms part, your original formula would have worked as the issue was down to trying to use two symptoms.

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
hm, just wondering why the averageifs didn't work though

I told you that....because it had two criteria for symptoms and was trying to find rows that matched symptom 1 AND symptom 2, rather than symptom 1 OR symptom 2

The array entered suggestion from Rory included the Range A1:A2 of symptoms in one criteria rather than each in its own criteria.

Thanks
Rob H
0
 
agwalshAuthor Commented:
Worked beautifully in the end. Can see alot of scope for how this would work...and gave me additional insight into the SumIFS/CountifS function. Thanks as always...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now