View original (French)

"IF" formula with multiple conditions in LibreOffice

Solved
JeanBartassant Posted messages 17 Status Membre -  
JeanBartassant Posted messages 17 Status Membre -
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 réponses

Réponse 1 / 3
diablo13800 Posted messages 3469 Registration date   Status Membre 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
JeanBartassant Posted messages 17 Status Membre 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