Explain formula

[Sarah7828]

=IF(AND($A1="I",$C1>$B1),($B1-$C1),IF(AND($A1="I",$C1<$B1),($B1-$C1),IF(AND($A1="E",$C1>$B1),($B1-$C1)*(-1),IF(AND($A1="E",$C1<$B1),$C1-$B1,IF(ROUND($C1,2)=ROUND($B1,2),"0","")))))

I found this formula in a thread posted on this platform . It is a good formula for management reporting

I am just understand why is there a bracket at the on $b1-$c1, when income (I) in $c1 is greater than income in $b 1 , but with expense (E) when the expense in $c1 is less than expense in $b1 , there is no brackets on $c1 - $b1? Why?

Thanks in Advance

 

[6StringJazzer]

The brackets that you pointed out are not syntactically necessary in this particular formula. The formula would be the same with them or without them in all those places. The formula below is syntactically equivalent to the one you posted.

=IF(AND($A1="I",$C1>$B1),$B1-$C1,IF(AND($A1="I",$C1<$B1),$B1-$C1,IF(AND($A1="E",$C1>$B1),($B1-$C1)*(-1),IF(AND($A1="E",$C1<$B1),$C1-$B1,IF(ROUND($C1,2)=ROUND($B1,2),"0","")))))

And you didn't ask, but
($B1-$C1)*(-1)
could be written as
$C1-$B1
I have no clue as to why it was done the the first way in one place, but the second way in another.

Note: In the U.S. we call them "parentheses"

 

[Alex Blakenburg]

I would take it one step further. To start with you definitely don't want the 0 in quotation marks that will give you a text value of 0 not the number 0.
Since the last condition indicates that we are only working to 2 decimal places and since you have O 2021, you could do this:
(Give diff a more meaningful name)

= LET(diff,ROUND($B1-$C1,2),
      IF($A1="I",diff,IF($A1="E",-1*diff,"")))

Without Let it would look like this.

=IF($A1="I",ROUND($B1-$C1,2),IF($A1="E",-1*ROUND($B1-$C1,2),""))

 

[Sarah7828]

The brackets that you pointed out are not syntactically necessary in this particular formula. The formula would be the same with them or without them in all those places. The formula below is syntactically equivalent to the one you posted.

=IF(AND($A1="I",$C1>$B1),$B1-$C1,IF(AND($A1="I",$C1<$B1),$B1-$C1,IF(AND($A1="E",$C1>$B1),($B1-$C1)*(-1),IF(AND($A1="E",$C1<$B1),$C1-$B1,IF(ROUND($C1,2)=ROUND($B1,2),"0","")))))

And you didn't ask, but
($B1-$C1)*(-1)
could be written as
$C1-$B1
I have no clue as to why it was done the the first way in one place, but the second way in another.

Note: In the U.S. we call them "parentheses"

Thank you . Noted- parentheses. We use in AUS

 

[6StringJazzer]

Yeah I try to be bilingual, I was just noting it for the benefit of other Yanks.

Keep saying brackets

 

[MARK858]

How we used to be taught it in England... brackets were [] ,parentheses were () and braces were {}

 

[6StringJazzer]

How we used to be taught it in England... brackets were [] ,parentheses were () and braces were {}

That's exactly what we say, but others sources say the Brits call them all "brackets."

parentheses and brackets

separatedbyacommonlanguage.blogspot.com

 

[MARK858]

In common speech people over here tend to say curly brackets, square brackets and round brackets, but it isn't what you get taught in English lessons at school (or least all the way back when I was taught, you would be marked down)

 

[myall_blues]

In common speech people over here tend to say curly brackets, square brackets and round brackets, but it isn't what you get taught in English lessons at school (or least all the way back when I was taught, you would be marked down)

In Australia it would normally be curly brackets, square brackets and brackets (on its own for the default round).