View original (French)

"IF" formula with multiple conditions in LibreOffice

Solved
JeanBartassant Posted messages 17 Status Member -  
JeanBartassant Posted messages 17 Status Member -
Hello,

I'm trying to include a formula in Libre Office, but I keep getting error codes. Can someone tell me where it's going wrong:

I would like to get a different result in B depending on the value of A, such as:

=IF(A<250;"3.15";IF(AND(A>250;A<500);"4.3";IF(AND(A>500;A<1000);"5.45"; ...)

It's simple, I'm sure I managed to do it a few years ago, but now I can't seem to find it. There's a syntax problem, but which one?.....

Thanks in advance!

Configuration: Windows XP / Firefox 41.0

3 answers

  1.
    diablo13800 Posted messages 3469 Registration date   Status Member Last intervention   1 872
     
    Here is how I understand and would write it:
    =IF(E<3, IF(P<250, A="3.15€", IF(P<500, A=(Price for E<3cm and 250<P<500), IF(P<1000, A="(Price for E<3CM and 500<P<1000)", IF(Continue up to 3 Kg))))), IF(P<250, A="5.50€", IF(P<500, A=(Price for E>3cm and 250<P<500), IF(P<1000, A="Price for E>3CM and 500<P<1000", IF(Continue up to 3KG)))))

    In a clearer version, it would look like this 

    If E<3 //We are in letter format Followed
     If P<250
     A=3.15
     Otherwise
     If P<500
     A="Price for E<3cm and 250<P<500
     Otherwise
     If P<1000
     A="Price for E<3cm and 500<P<1000
     Otherwise 
     Etc
     End If
     End If
     End If
    Otherwise // We enter Colissimo format
     If P<250
     A=550
     Otherwise
     If P<500
     A="Price for E<3cm and 250<P<500
     Otherwise
     If P<1000
     A="Price for E<3cm and 500<P<1000
     Otherwise 
     Etc
     End If
     End If
     End If 
    End If


    Is this something like what you would want?
    1
    1. JeanBartassant Posted messages 17 Status Member 1
       
      Absolutely! I developed your formula and tested it with the actual values from La Poste to date (you never know, you might need it): it works perfectly. Here it is (I didn't put the values of "A" in parentheses - which would return a "Text" - as I don't need to show the currency, the number alone is enough):

      IF(E<3,IF(P<250,3.15,IF(P<500,4.3,IF(P<1000,5.45,IF(P<2000,6.9,IF(P<3000,7.5))))),
      IF(P<250,5.5,IF(P<500,6.45,IF(P<1000,7.9,IF(P<2000,8.95,IF(P<5000,11.5))))))

      A big thank you for your help. As a result, I dove into spreadsheet tutorials that can be found online: it's better than Sudoku!
      I'm marking the question as "Resolved."
      0