Numbers - Complex If And Then Statement Using Checkboxes In One Formula

Hi SG,

I note your screenshot doesn't include an example with none of the boxes checked. As written, I think your formula will return a division by zero error in that case, as mine did before I replaces zero with "" in the Aux table formula, and added the IF wrapper to the formula in column D of Table 1.

A quick fix to prevent your formul from throwing this error is shown below:

D2: (IF(A2,A$1,0)+IF(B2,B$1,0)+IF(C2,C$1,0))/MAX(1,COUNTIFS($A2:$C2,TRUE))

Regards,

Barry

Rephrasing your description, I get:

I have three checkboxes.

Each is assigned a different 'price'

If one or more box is checked, I want to display th AVERAGE of the price(s) assigned to the checked boxes.

If no box is checked, display 0.00.

Here is a solution using an auxiliary table.

I'm on a slow internet connection, so screen shots will have to wait. Here are descriptions of the main table (Table 1) and the Auxilliary table (Aux).

Table 1:

Row 1 is a header row.

Column A is a Header column.

Columns B, C and D contain checkboxes.

Column E contains the formula below, entered in D2, and filled down to D9:

D2: IF(COUNTIF(B2:D2,TRUE)<1,0,AVERAGE(Aux::A2:C2))

COUNTIF counts the number of checked boxes in 'tis row' of columns B to D.

IF the count is less than one, the formula places a 0 in the cell, and the data format of the cell displays this as 0.00.

If the count is not less than 1 (at least 1 box is checked), the formula places the AVERAGE of the prices assigned to the checked boxes (which it gets from 'this row' of the Aux table) in the cell.

Aux

Row 1 is a Header row.

Rows 2 through 9 are body rows.

Cells A1, B1, and C1 contain the prices assigned to the three checkboxes in columns B, C and D of Table 1.

The example uses 10.00, 20.00 and 40.00 in these columns.

The rest of the cells (A2 - C9) contain the formula below, entered in A2, then filled right and down to C9.

A2: IF(Table 1::B2,A$1,"")

The formula inserts the price in row 1 of 'this column' is the associated checkbox is checked ( true )

If the associated box is NOT checked ( false ), the formula inserts a null string ( "" ), a text value, that is ignored by AVERAGE.

Here is a list showing which boxes are checked, and the result:

A:          10.00

B:          20.00

C:          40.00

AB:        15.00

AC:        25.00

BC:        30.00

ABC:     23.33

- - -:        0.00

Images when I get to a faster connection.

Regards,

Barry

Screen shots to go with my post above.

Showing formula in Aux::A2.

Showing formula in Table 1::D2.

Note: the original version of this formula had "" where this formula has 0, producig the results in rows 9 and 10 of the first image.

Rgards,

Barry

Hi SG,

Or perhaps it depends on what Brian specified in his original post :

If Checkbox 1 is checked, then display Price 1

Then if Checkbox 1 is NOT checked, but Checkbox 2 is checked, then display Price 2

Then if Checkbox 1 is NOT checked, & Checkbox 2 is NOT checked, but Checkbox 3 is checked, then display Price 3

Then if Checkbox 1 & Checkbox 2 is checked, then (Price 1 + Price 2) /2, displaying the average of Price 1 & Price 2

Then if Checkbox 1, 2, & 3 is checked, then (Price 1 + Price 2 + Price 3) /3, displaying the average of Price 1, Price 2, & Price 3

Then if Checkbox 1, 2, or 3 is NOT checked, then 0

Regards,

Barry

Also one trick I use the give a blank cell instead of a “0” is to add a conditional format that if the value is zero make the text white or in this case yellow. The value is still there but hidden.

If the cells are blank the average function in the total will error. You can also add an off error function to correct that to “0”

IFERROR(COUNTIF(D19:F19,">0")÷SUM(D19:F19),0)

Hey Brian,

i took a look look at the document. The error is caused when all values equal 0. The remedy this the easiest way is to wrap the whole function in an iferror function like this:

(this is the Customer 3 function)

IFERROR(IF(COUNTIF(C3:C5,TRUE)<1,0,SUM(D9:F9)÷COUNTIF(D9:F9,">0")),0)

the “,0)” at the end would represent what you want the value to be if the function runs into an error. I don’t recommend using this too extensively but in this situation it would be the simplest solution.

You could do do this for each cell calculating an avg.

Hope this helps.

Steve

Hi Barry,

Perhaps it depends on whether one normally has all boxes unchecked. I like keeping formulas simple and letting Numbers alert me, as warning triangles are designed to do.

SG

I think you were over complicating it a bit. I was able to get it working using 2 different tables and filters to show or hide the prices I want.

I used an IF,AND Function to set each combination of checkboxes and set it to place either “show” or “hide” in column B of table 2.

Then I set a filter to only show the row that had “show”

I set the price values as $5, $10 and $20. I then set functions to get the avg of price 1 and 2 then price 2 and 3 then 1,2 and 3.

Here is the function for checkbox 3 or Table 1 C2:

IF(AND(Table 1::$A$3,Table 1::$B$3,Table 1::$C$3=FALSE),"show","hide")

Here are a few screen shots: (sorry about the size)

If you don't want to set up an extra table, you can easily do this with just one formula, like this:

The formula in D2, filled down:

=(IF(A2,A$1,0)+IF(B2,B$1,0)+IF(C2,C$1,0))/COUNTIFS($A2:$C2,TRUE)

All this does is add the value if its corresponding box is checked, otherwise put zero, and then divide the sum by the number of boxes that were checked.

SG

Hey Brian,

If I understand what you are having an issue with is the average of only the prices that are showing rather than all 3 if one is blank.

This is solved it in example 2.

SUM(G19:G21)÷COUNTIF(G19:G21,">1")

rather than using the average function that uses the same array each time I tested to see how many numbers were greater than zero first then divided by that number instead.

Hope this helps.

Steve

Responding to your original post, not your latest with the additional question (because I did not see it before posting)...

Starting with Barry's rephrasing of your problem and his description of the table with the checkboxes and his inclusion of an AUX table to conatin the prices, a formula for cell Table 1::E2 would be:

=IFERROR(AVERAGEIF(B2:D2,TRUE,AUX::A2:C2),0)

While not recommended, if you would rather have the prices in the formula (not in a table), the formula would be like this one (the prices in this formula are 10, 20, and 30):

=IFERROR(AVERAGEIF(B2:D2,TRUE,{10,20,30}),0)