# Boolean problem

#### Jubjab

##### Well-known Member
Maybe it's because it's Friday afternoon, but I can't seem to find an elegant way to solve this small problem.

I have a report which shows sales data. The data is taken from 2 sheets with the raw data: "allsalesdata" which includes all deliveries, and "freeofcharge" which shows free of charge deliveries. Note! The "allsalesdata" also includes the free of charge deliveries!

In my report I have two checkboxes, so the user can select "Show only real sales" and "Show free of charge". So the user has options to see the data in 4 ways:

Real Sales = TRUE
Free of charge = TRUE
--> show figure from allsalesdata only

Real Sales = TRUE
Free of charge = FALSE
--> show figure from allsalesdata and deduct figure from freeofcharge

Real Sales = FALSE
Free of charge = TRUE
--> only show figure from freeofcharge

Real Sales = FALSE
Free of charge = FALSE
--> show zero

As the formula to fetch the data is quite long, and I have a lot of rows, I don't want to make a 4 clause IF formula. Any suggestions?

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
What's your "formula to fetch the data"?

One way to split 2 Booleans into the four options is

=CHOOSE(1+(RealSales=TRUE)+2*(Free=TRUE), "neither", "real not free", "free not real", "both")

Looks elegant, will try it first thing monday morning.

@Andrew: it's just a regular iferror(index+match) formula, but as it references other files it gets quite long.

In the case of FALSE/TRUE, did you want to show the Free of Charge amount as positive or negative?

Positive.

Thanks mikerickson, this worked perfectly!

Replies
17
Views
6K
Replies
0
Views
290
Replies
0
Views
894
Replies
1
Views
4K
Replies
19
Views
982

1,196,467
Messages
6,015,403
Members
441,892
Latest member
gpen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back