Multiple If & And formula

English
#1

Can someone help with this formula. I keep getting Err.508 messages, and no matter what I try, regarding parentheses, the problem persists. The solution may be simple, but I’ve been hacking away at it all afternoon. The formula is in Column N (Row 162).

=IF(J162<>"-",IF(AND(K162="Incl.",M162="Ea."),I162*(J162+L162),IF(AND(K162="Incl.",M162="All"),I162*J162+L162,IF(AND(K162="Plus",M162="All'),(I162*J162+L162)*1.1,IF(AND(K162="plus",M162="ea."),I162*(J162+L162)*1.1,"-"")))))

The spreadsheet headings are (and examples of how it is intended to work):

I162 J162 K162 L162 M162 N162
Qty $ Ea. Tax Incl./Plus Delivery Ea./All PriceTotal (Tax is 10%)
4 $10 Incl. $5 Ea. $60
4 $10 Incl. $5 All $45
4 $10 Plus $5 All $49.50
4 $10 Plus $5 Ea. $66

(The purpose of the spreadsheet is to keep track of on-line orders and purchases, where different suppliers have differing pricing, delivery and tax methods).

Note also the lack of capitalisation on the last “plus” and “ea.” I’ve tried to correct this in the formula, but I will not accept the changes. Grrr. ???

#2

Note: When I C&P’d the formula to this post, it dropped some " * " 's from the formula, and pasted half of it in italics. ?? So in three places, there are " * " 's missing just after the I162’s.

I’ll try to re-copy and Paste here:
=IF(J162<>"-",IF(AND(K162="Incl.",M162="Ea."),I162*(J162+L162),IF(AND(K162="Incl.",M162="All"),I162*J162+L162,IF(AND(K162="Plus",M162="All'),(I162*J162+L162)*1.1,IF(AND(K162="plus",M162="ea."),I162*(J162+L162)*1.1,"-"")))))

The ‘Table’ that I carefully typed in with spaces, for clarity, has lost its nice column and row effect. Sorry, I hope it still makes sense.

#3

Try editing the message: select the formula text and use this editor feature
image

#6

Thank you for the tip, I’ll remember to use it next time. For now though, the intended format / appearance of both the ‘Table’ and the formula has corrected itself. :slightly_smiling_face:

#4

Hi,
On the “Plus” and “All” IF, should the calculation be +I162*(J162+L162)

Full formula

IF(J162<>"-",IF(AND(K162="Incl.",M162="Ea."),I162*(J162+L162),IF(AND(K162="Incl.",M162="All"),I162*J162+L162,IF(AND(K162="Plus",M162="All"),(I162*J162+L162)*1.1,IF(AND(K162="Plus",M162="Ea."),+I162*(J162+L162)*1.1)))),"-")

Produced using Windows 10 home, LO 24.2.0.3

#5

Hi Gregors15. I think it is correct as shown. It is meant to multiply the # of items x the price of items, B4 tax, add a one off delivery charge (as opposed to a delivery charge per item), and then add 10% tax to the lot.

“All” means that the delivery charge covers all items together.

The formula that you have suggested works for “Plus” and “Ea.”

Thank you for looking at this. I’m still trying to nut it out. // Dominic
BTW, the missing " * " 's have returned to their rightful places.

#7

In the middle of your formula

There is an apostrophe (single quote) where you meant to have a double quote. Fix that and remove the autoinserted extra double quote at the end (before the closing parentheses).

2 Likes
#8

keme1: THANK YOU !!! To the moon and back!!! Yay! Go LibreOffice!!!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.