How do I exclude a cell from a range when the cell is blank?

Question

Level 1

1 points

How do I exclude a cell from a range when the cell is blank?

I am trying to improve an existing spreadsheet that my teachers use. The spreadsheet is organized by grade categories i.e. Grammar, Phonics, Vocabulary Tests, Spelling Tests, ect. As you can imagine kids miss classes and some activities can not be made up. This results in a missing grade. I would like to remove that missing grade from their average and their total possible points. Both steps are required for an accurate grade. Also we are not using weighted grades. In excel I have found a way to do this and modified it for our needs but it does not work in Numbers. This is the formula:

=IFERROR(SUMPRODUCT($B$4:$O$4,--(B6:O6<>"X")),"")

Numbers tells me there is a syntax error and this ^ is converted to ≠

In this formula an X is placed in cells that need to be excluded. Below is an example. There is some extraneous data points in the top right cells that are a hack to handle new students added to the class midway through the grading period. The focus here is on the highlighted cells. Again this works fine in Excel.

Does anyone have any suggestions? Any idea on fixing the syntax?

MacBook Air (13-inch, Early 2014), iOS 8.4.1

Posted on Sep 8, 2015 9:58 PM

Reply

Answer 1

Barry

Level 9

60,023 points

Sep 9, 2015 2:45 AM in response to KyleMullaney

Hi Kyle,

Simplify.

You are calculating the grade as the fraction of points earned/points possible, expressed as a pecentage.

Use SUMIF to determne the points possible.

Use SUM to total the points earned.

Divide the second by the first to determine fraction of the possible total, and format the result as a percentage.

P6: =SUMIF(B6:O6,"<>x",$B$3:$O$3)

Q6: =SUM(B6:O6)

R6: =(Q6/P6)

Regards,

Barry

Reply

Answer 2

KyleMullaney Author

Level 1

1 points

Sep 9, 2015 2:46 AM in response to Barry

Thanks Barry that is exactly what I needed. I appreciate it.

🙂

Reply

Answer 3

Barry

Level 9

60,023 points

Sep 9, 2015 2:47 AM in response to KyleMullaney

Regarding the syntax error:

=IFERROR(SUMPRODUCT($B$4:$O$4,--(B6:O6<>"X")),"")

The first error I see is in the first range reference. Row 4 contains Annie's scores, which should have no bearing on Benson's average. 😉

As written, you should not get an error triangle, as any error in the main formula will be trapped by IFERROR. IFERROR is useful if you are expecting errors to occur in the normal course of events, know what those errors will be, and know that trapping the error and substituting a fixed result (in this case a null string) is an acceptable substitution. During initial development and testing of a formula or spreadsheet, IFERROR should be avoided. Knowing what errors occur is important at that stage.

Replacing <> with ≠ is normal with Numbers 3. If I recall correctly, it also replaces *, used as the multiplication operator, with x.

Dropping IFERROR from the formula, I do get an error triangle, but it is not flagging a syntax error:

In Numbers, SUMPRODUCT is more narrowly defined than in Excel. It does what its name implies: returns the SUM of the PRODUCTs of corresponding numbers in one or more ranges. With a single range, it returns the sum of the numbers in that range. With two ranges, as in the example below, it multiplies each pair of numbers, then sums those products, Text values (eg. "x") and empty cells are evaluated as zero. Boolean values are ignored.

Results here are (1X1)+(2x2)+(3x3)+(1x0)+(2x0)+(3x0)+(3x0)+(2x0)+(1x0)+(0x0)+(0x0)+(0x0)+(0x0) = 14

Regards,

Barry

Reply