Ask Your Question

# Libre Calc If & And Statements (True/False) Could anyone shed some light on the following. My Table is as below.

Payment  Join Date   Mthly Fees  Annual Fees  Jan    Feb
Monthly  2017/01/25  $10.00$120.00      1      FALSE
Annual   2016/02/20  $20.00$240.00      FALSE  FALSE
Monthly  2017/02/22  $30.00$360.00      FALSE  TRUE

=IF(AND($A$9="Monthly",MID($B$9,6,2)="01",$C$9,$D$9))


Have checked the formatting of each Cell and set these to either Text or Date. In the result under 'Jan' (as shown by the value of 1) has been set to Boolean whereas all others are set to Text.

I am trying to extract the Value (in $) for each month. If a member is Monthly payment, then the value in Jan would be$10, Feb = $240 (Annual) etc. If I use specific values as below, it works Value 1 Value 2 Value 3 Value 4 Result Yes Monthly 10 120 10 Yes Annual 20 240 240 =IF(AND(A3="Yes",B3="Monthly"),C3,D3)  If anyone could explain, that would be appreciated. Cheers. edit retag close merge delete ## Comments 1 You could want to check why in working case, you have brackets like =IF(AND(<COND1>,<COND2>),<THEN>,<ELSE>) , and in failing case you have =IF(AND(<COND1>,<COND2>,<THEN-AS-COND3>,<ELSE-AS-COND4>)). And don't forget to change cells' format back to numeric from boolean which was automatically applied when you used formulas that returned logical values. Thanks Mike, I thought the Syntax was essentially the same, except I have nested another formulae in there <mid($b$9,6,2)&gt; anyway,="" i="" did="" discover="" a="" better="" way="" to="" achieve="" the="" result="" by="" using="" sumifs="" function,="" but="" do="" appreciate="" the="" reply="" thanks="" mike<="" p=""> ## 1 Answer Sort by » oldest newest most voted The corrected formula: =IF(AND($A9="Monthly";MID($B9;7;2)="01");$C9;$D9)  • As @Mike_Kaganski hinted, the parentheses were misplaced in the first formula. • It looks like 6 should be 7 in the MID statement. • These ranges should be $A9 rather than $A$9 so that the formulas adjust by row when filled.

Example file: Monthly or Annual Payment.ods

more

## Comments

Thanks Jan, I see your point about the parentheses. Sharp eyes! I used 6 in the Mid statement so I could capture months 10,11,12 The $A9 was only a little test that I had done, but see your point. As replied to Mike I used the following to get a better solution from where I was originally heading. =SUMIFS($AW$2:$AW$3022,$AP$2:$AP$3022,$AP$2,$M$2:$M$3022,"01")+SUMIFS($AW$2:$AW$3022,$AP$2:$AP$3022,$AP\$7) Just have to change the "01" to "02" for Feb etc. Cheers

## Stats

Asked: 2018-05-08 13:47:06 +0200

Seen: 2,775 times

Last updated: May 08 '18