Boolean problem

[Jubjab]

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?

 

[Andrew Poulsom]

What's your "formula to fetch the data"?

 

[mikerickson]

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")

 

[Jubjab]

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.

 

[Scott Huish]

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

 

[Jubjab]

Positive.

 

[Jubjab]

Thanks mikerickson, this worked perfectly!