Solved

problems with averageifs formula

Posted on 2015-02-04
8
65 Views
Last Modified: 2015-02-11
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
Comment
Question by:agwalsh
  • 4
  • 3
8 Comments
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 40588432
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
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 40588442
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40588452
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
 

Author Comment

by:agwalsh
ID: 40588647
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:agwalsh
ID: 40588700
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40588859
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
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40588874
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
 

Author Closing Comment

by:agwalsh
ID: 40602564
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vb.net dynamic formulas in runtime 11 61
macro for each dropdown 15 44
Pivot help - Display only Is Not Null 7 15
Problem with Excel and File Size 7 24
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now