New Formula Idea : Convert boolean into a number and stop IF BOOLEAN THEN X ELSE XX

Nico

Hello community,

I have to do a lot of controls (boolean) and base and theses controls give me a value based on its true or not.

I do a lot of these technicals line items for :
-Controlling element
-Color Coding
-...

I code it in 3 steps :

• 1) Business Element (Number) : Uselly a business value
• 2) Control (Boolean) : Management rules that I want to verify (ex : Business Element > 0)
• 3) Color Coding (Number) : Based on the control I will apply a value in order to build a color coding.

 

It is not very complicated to do. Nevertheless, this 3rd step forces me to write it this way :
Color Coding = IF Control THEN XX ElSE XX

I try to split my line items when I can in order to have the best lisibility but this 3rd step create complexity where there is not.

It would be great to have a direct formula to convert a boolean into a number. I image a formula that would be such as :
CONVERT(Condition, true value result, false value result)

Where :
Condition : Boolean LI
True value result : Number
False value result : Number
Source : Boolean
Output : Number

Best regards,
NT

Nico

Thank you George for you answer. I will deep dive a little more.


Would it be the same as IF ?
Well, All the formulas I have seen in Anaplan have a designed format result. Not sure, Anaplan has the capability to have a same formula that would give different format result.

Auditability & Performance of the IF/THEN/ELSE :

The problem its not really breaking line items. Breaking line items will lead  to improve performance in your model. According to best practices, if size isn't an issue you should break as much as possible your line items to improve performance. Breaking them should also give you more lisibility.

However, It is more the visibility of that 3rd LI that distrubed me. Indeed, you break your line items to improve performance and also lisibilty. Nevertheless, at the end you still have to do an IF/THEN/ELSE statement that is complex/un natural to read.

Moreover, IF/THEN/ELSE statement is a formula that is likely to underperform. In order to have the best performance in your model you should write your IF/THEN/ELSE statement in a way that the "THEN" condition would be the more likely to happened than the "ELSE" statement.

Therefore, for a simple request you would have to add a complex approach and sometimes you would have to write your line item such as IF NOT Condition THEN False Result ELSE True Result. 

Saying so, in order to have a model as efficient as possible you loose a lot of lisibility treating with boolean. Thus, it would be great to have a fonction better designed function then IF/THEN/ELSE to improve both aspects :

• Lisibility in order to be understood in a second
• Performance by calculating at the same time the 2 possibles results.

Nico

Great talk here,

I never used excel too much. However, in all my other programming languages, I was always advised against using "IF/THEN/ELSE" because there were always more advanced features.  

We are aligned with the expression of the condition and its writing. I wrote the expression "NOT" to be as clear as possible.

What I understand about "IF THEN ELSE" is that the function calculates all the time the "THEN" and if the condition is not respected the "THEN" and the "ELSE".

That's why we are asked as developers to flip our condition sometimes.

The performance should be agnostic of the way the condition is written. I though calculating the two possibles outcomes and display only the value depending on the condition would be more efficient.

Maybe the solution is a more advanced While() loop ?