Ask Your Question
0

Libre Calc If & And Statements (True/False)

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

Ding Duck gravatar image

updated 2018-05-08 14:37:44 +0200

Jim K gravatar image

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 flag offensive 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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-05-08 13:57:42 +0200 )edit

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

Ding Duck gravatar imageDing Duck ( 2018-05-09 08:11:23 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-05-08 14:50:41 +0200

Jim K gravatar image

updated 2018-05-08 14:57:28 +0200

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

edit flag offensive delete link 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

Ding Duck gravatar imageDing Duck ( 2018-05-09 08:25:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 2,775 times

Last updated: May 08 '18