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

So I have this conundrum I am dealing with. I am trying to get one formula to work properly in one field, so the information is displayed in only that one field. This formula I need help with involves multiple check boxes and prices. This seems simple, but it is more complex than it seems. I know I am very close, but yet still not quite there yet. Here is a link to my Google Drive of an example showing exactly what I am trying to do: Checkboxes & Prices Formula. The formula in question is in the fields filled with Yellow.


Here is the break down of conditions I am trying to fulfill in one formula (I believe I got all the conditions down):


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


As you can see, this can get VERY complex as more check boxes and prices are added. Is there any way to get this to work right, and possibly an easy way to add to the formula as more customers and prices are added?


I have come up with 3 different formulas, all of course don't work (2 don't work at all), but the first seems VERY close. Each uses a completely different way of looking/calculating this situation. With all three formulas, I only got as far as having two checkboxes and two prices. I have not factored in a 3rd check box or price just yet, since it's getting pretty complex, and I've been focusing on getting a solid formulas with 2 first.


I greatly appreciate anyone's and everyone's help. Thank you in advance!

iMac 27" 5K, macOS 10.14

Posted on Apr 24, 2019 5:17 PM

Reply
16 replies

May 9, 2019 8:26 PM in response to briangmetal

Now responding to your latest post and basing my response on your spreadsheet:


I would make a few changes.


It would have been easier if the checkboxes were in a row vs in a column but it is still workable like you have it.


You do not need anything in the range of D6:F9. I made no changes to anything there because I didn't use any of it. Maybe you could put the prices there instead of in the Prices table.


If a customer does not offer a product for sale, do not put a zero in the Prices table, leave the cell blank. I deleted all zeros from that table. Blank cells will not be tallied in AVERAGEIF. Zeros will, though.


The formula for cell G7 =IFERROR(AVERAGEIF($C$3:$C$5,TRUE,TRANSPOSE(Prices::B2:D2)),"")

It required TRANSPOSE because the checkboxes are vertical but the data is arranged horizontally.

AVERAGEIF will not include blank cells in the average (as mentioned above)

If it gets a divide by zero, the answer will be forced to "" (a null string).

Fill down to G8 and G9.


G11 =AVERAGE(G7:G9)

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

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

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.