asked on 2/4/2015

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

Microsoft Excel

Last Comment

agwalsh

2/11/2015

SOLUTION

Rob Henson

2/4/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Rob Henson

2/4/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Rory Archibald

2/4/2015

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))

agwalsh

2/4/2015

ASKER

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..

agwalsh

2/4/2015

ASKER

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

Rob Henson

2/4/2015

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

ASKER CERTIFIED SOLUTION

Rob Henson

2/4/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

agwalsh

2/11/2015

ASKER

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...