a 'character' is spoiling my 'averages'

[OneLooseCrank]

Hi all,
I am averaging grades across a term but on occasion have been using an 'x' to denote work not completed. I've realised that using any character halves the reported average grade. I'd like to include characters to denote certain things when no grade has been taken in. Can anyone explain what Excel is doing / assuming where I am using characters in an average formula where no number is available and if there is a way of using characters in a way the average will ignore?
Thanks

 

[FormR]

Hi, the AVERAGE() function ignores text and blanks, can you post the formula you are using.

Excel 2013/2016
	A	B	C	D
1	10	5	X	7.5
Sheet1

Cell Formulas
Range	Formula
D1	=AVERAGE(A1:C1)

 

[OneLooseCrank]

Hi, the AVERAGE() function ignores text and blanks, can you post the formula you are using.

Excel 2013/2016

	A	B	C	D
X

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]7.5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=AVERAGE(A1:C1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yeah... Um, here you go:

=IF(((IF(SUMIF($N$4:$AE$4,$H$4,$N7:$AE7)>0,SUM(SUMIF($N$4:$AE$4,$H$4,$N7:$AE7)*SUMIF($N$4:$AE$4,$H$4,$N7:$AE7)),"0"))+IF(SUMIF($AF$4:$AN$4,$H$4,$AF7:$AN7)>0,SUM(SUMIF($AF$4:$AN$4,$H$4,$AF7:$AN7)*$AF$2),"0")+IF(SUMIF($AO$4:$AZ$4,$H$4,$AO7:$AZ7)>0,SUM(SUMIF($AO$4:$AZ$4,$H$4,$AO7:$AZ7)*$AO$2),"0")+IF(SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7)>0,SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7)*$BA$2,"0")+IF(SUMIF($BI$4:$BK$4,$H$4,$BI7:$BK7)>0,SUMIF($BI$4:$BK$4,$H$4,$BI7:$BK7)*$BI$2,"0")+IF(SUMIF($BL$4:$BX$4,$H$4,$BL7:$BX7)>0,SUMIF($BL$4:$BX$4,$H$4,$BL7:$BX7)*$BL$2,"0"))>0,(IF(SUMIF($N$4:$AE$4,$H$4,$N7:$AE7)>0,SUM(SUMIF($N$4:$AE$4,$H$4,$N7:$AE7)*$N$2),"0")+IF(SUMIF($AF$4:$AN$4,$H$4,$AF7:$AN7)>0,SUM(SUMIF($AF$4:$AN$4,$H$4,$AF7:$AN7)*$AF$2),"0")+IF(SUMIF($AO$4:$AZ$4,$H$4,$AO7:$AZ7)>0,SUM(SUMIF($AO$4:$AZ$4,$H$4,$AO7:$AZ7)*$AO$2),"0")+IF(SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7)>0,SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7)*$BA$2,"0")+IF(SUMIF($BI$4:$BK$4,$H$4,$BI7:$BK7)>0,SUMIF($BI$4:$BK$4,$H$4,$BI7:$BK7)*$BI$2,"0")+IF(SUMIF($BL$4:$BX$4,$H$4,$BL7:$BX7)>0,SUMIF($BL$4:$BX$4,$H$4,$BL7:$BX7)*$BL$2,"0"))/(IF(COUNTIFS($N$4:$AE$4,$H$4,$N7:$AE7,"<>" & "")>0,COUNTIFS($N$4:$AE$4,$H$4,$N7:$AE7,"<>" & "")*$N$2,"0")+IF(COUNTIFS($AF$4:$AN$4,$H$4,$AF7:$AN7,"<>" & "")>0,COUNTIFS($AF$4:$AN$4,$H$4,$AF7:$AN7,"<>" & "")*$AF$2,"0")+IF(COUNTIFS($AO$4:$AZ$4,$H$4,$AO7:$AZ7,"<>" & "")>0,COUNTIFS($AO$4:$AZ$4,$H$4,$AO7:$AZ7,"<>" & "")*$AO$2,"0")+IF(COUNTIFS($BA$4:$BH$4,$H$4,$BA7:$BH7,"<>" & "")>0,COUNTIFS($BA$4:$BH$4,$H$4,$BA7:$BH7,"<>" & "")*$BA$2,"0")+IF(COUNTIFS($BI$4:$BK$4,$H$4,$BI7:$BK7,"<>" & "")>0,COUNTIFS($BI$4:$BK$4,$H$4,$BI7:$BK7,"<>" & "")*$BI$2,"0")+IF(COUNTIFS($BL$4:$BX$4,$H$4,$BL7:$BX7,"<>" & "")>0,COUNTIFS($BL$4:$BX$4,$H$4,$BL7:$BX7,"<>" & "")*$BL$2,"0")),$H$2)

You are looking at a weighted average function. It tests a string against another string, adds up all cells the meet the criteria, counts the number of items attributed, multiples the average item by a weighting and divides it by the number of items counted. And it does this for five different criteria over 6 different cycles / weightings.
And as mentioned, when a character arrives instead of a number, it halves the weighted average.

 

[thisoldman]

Simplified, your formula becomes this:

=IF((SUMIF($N$4:$AE$4,$H$4,$N7:$AE7) * SUMIF($N$4:$AE$4,$H$4,$N7:$AE7)
    + SUMIF($AF$4:$AN$4,$H$4,$AF7:$AN7) * $AF$2
    + SUMIF($AO$4:$AZ$4,$H$4,$AO7:$AZ7) * $AO$2
    + SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7) * $BA$2
    + SUMIF($BI$4:$BK$4,$H$4,$BI7:$BK7) * $BI$2
    + SUMIF($BL$4:$BX$4,$H$4,$BL7:$BX7) * $BL$2),
  (SUMIF($N$4:$AE$4,$H$4,$N7:$AE7) * $N$2
      + SUMIF($AF$4:$AN$4,$H$4,$AF7:$AN7) * $AF$2
      + SUMIF($AO$4:$AZ$4,$H$4,$AO7:$AZ7) * $AO$2
      + SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7) * $BA$2
      + SUMIF($BI$4:$BK$4,$H$4,$BI7:$BK7) * $BI$2
      + SUMIF($BL$4:$BX$4,$H$4,$BL7:$BX7) * $BL$2)
    / (COUNTIFS($N$4:$AE$4,$H$4,$N7:$AE7,"<>" & "") * $N$2
      + COUNTIFS($AF$4:$AN$4,$H$4,$AF7:$AN7,"<>" & "") * $AF$2
      + COUNTIFS($AO$4:$AZ$4,$H$4,$AO7:$AZ7,"<>" & "") * $AO$2
      + COUNTIFS($BA$4:$BH$4,$H$4,$BA7:$BH7,"<>" & "") * $BA$2
      + COUNTIFS($BI$4:$BK$4,$H$4,$BI7:$BK7,"<>" & "") * $BI$2
      + COUNTIFS($BL$4:$BX$4,$H$4,$BL7:$BX7,"<>" & "") * $BL$2)
  ,$H$2)

You have some "SUM(SUMIFS(..." in your formula. The SUM portion of those statements is unneeded.

You also put quotation marks surrounding the zeros in your IF statements. This would return zero as a text string, but you end up using zero as a number. The quotes surrounding the zeros are unneeded.

I removed all but one IF. I assumed you have no negative numbers for grades.
Your statement:

IF(SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7)>0,SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7)*$BA$2,"0")

tests if the sumif is equal to zero. You don't have to test the SUMIF: sumif_result*$BA$2 will be zero when the SUMIF result is zero, otherwise it will be some_number_other_than_zero*$BA$2.

I applied the same reasoning to the COUNTIF statements. A count of items has to be greater than or equal to zero. The result of the multiplication will be zero when the count is zero, otherwise the result will be the product of two non-zero numbers.

I removed the test for greater than zero for the very first IF. When evaluating the conditional part of an IF statement, Excel coerces zero to the boolean value False. All other numbers are counted as True.

Your counts of values is off because your "x" marker is being counted as not equal to the empty string. In

COUNTIFS($AF$4:$AN$4,$H$4,$AF7:$AN7,"<>" & "")

if "x" appears in any of the cells AF7:AN7, those cells are added to the count.

That can be corrected to count only numbers:

(COUNTIF($AF$4:$AN$4,$H$4) + COUNT($AF7:$AN7))


Finally, Ive come up with this for you to try:

=IF((SUMIF($N$4:$AE$4,$H$4,$N7:$AE7) * SUMIF($N$4:$AE$4,$H$4,$N7:$AE7)
    + SUMIF($AF$4:$AN$4,$H$4,$AF7:$AN7) * $AF$2
    + SUMIF($AO$4:$AZ$4,$H$4,$AO7:$AZ7) * $AO$2
    + SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7) * $BA$2
    + SUMIF($BI$4:$BK$4,$H$4,$BI7:$BK7) * $BI$2
    + SUMIF($BL$4:$BX$4,$H$4,$BL7:$BX7) * $BL$2),
  (SUMIF($N$4:$AE$4,$H$4,$N7:$AE7) * $N$2
      + SUMIF($AF$4:$AN$4,$H$4,$AF7:$AN7) * $AF$2
      + SUMIF($AO$4:$AZ$4,$H$4,$AO7:$AZ7) * $AO$2
      + SUMIF($BA$4:$BH$4,$H$4,$BA7:$BH7) * $BA$2
      + SUMIF($BI$4:$BK$4,$H$4,$BI7:$BK7) * $BI$2
      + SUMIF($BL$4:$BX$4,$H$4,$BL7:$BX7) * $BL$2)
    / ((COUNTIF($N$4:$AE$4,$H$4) + COUNT($N7:$AE7)) * $N$2
      + (COUNTIF($AF$4:$AN$4,$H$4) + COUNT($AF7:$AN7)) * $AF$2
      + (COUNTIF($AO$4:$AZ$4,$H$4) + COUNT($AO7:$AZ7)) * $AO$2
      + (COUNTIF($BA$4:$BH$4,$H$4) + COUNT($BA7:$BH7)) * $BA$2
      + (COUNTIF($BI$4:$BK$4,$H$4) + COUNT($BI7:$BK7)) * $BI$2
      + (COUNTIF($BL$4:$BX$4,$H$4) + COUNT($BL7:$BX7)) * $BL$2)
  ,$H$2)

 

[OneLooseCrank]

Wow... You're like... Neo... I think you understand my formula better than I do and certainly deserve a beer for the time you've put into evaluating and writing up your response. A huge thank you. I'll need some time and my laptop to go through your comments and learn how to better write my formulas. I'll get back to the thread when I've tested your recommended revisions.