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?
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?