Hi Airtable Community!
I’m trying to set up a formula that will convert a printed price to its digital price equivalent, but the rules for those calculations are wonky. I was able to successfully tell the formula that for items under 10.99, it should subtract 1 or 2 from the printed price (depending on the original format), but my attempts to code a SWITCH clause for any titles over $10.99 (and thus subject to a specific price grid) are just returning empty values.
I’m trying to argue that if a book is hardcover and greater than 10.99, then the digital price from a grid should be swapped in based on the print price. Same for if the book is a paperback, but the grid values are different, so it requires its own clauses.
Here’s what I’ve got so far:
IF({US Price (Printed)}="",CONCATENATE(""),IF(AND({US Price (Printed)}!="",{US Price (Printed)}<'10.98',{Format Type}="Hardcover"),{US Price (Printed)}-'2.00',IF(AND({US Price (Printed)}!="",{US Price (Printed)}>'10.98',{Format Type}="Hardcover"),SWITCH({US Price (Printed)},'10.99','6.99','11.99','7.99','12.99','7.99','13.99','8.99','14.99','9.99','15.99','9.99','16.99','10.99','17.99','11.99','18.99','12.99','19.99','12.99','20.99','13.99','21.99','14.99','22.99','14.99','23.99','15.99','24.99','16.99','25.99','16.99','26.99','17.99','27.99','18.99','28.99','18.99','29.99','19.99','30.99','20.99','31.99','20.99','32.99','21.99','33.99','22.99','34.99','22.99','35.99','23.99','36.99','24.99','37.99','25.99','38.99','25.99','39.99','26.99','40.99','27.99','41.99','27.99','42.99','28.99','43.99','29.99','44.99','29.99','45.99','30.99','46.99','31.99','47.99','31.99','48.99','32.99','49.99','33.99','50.99','33.99','51.99','34.99','52.99','35.99','53.99','35.99','54.99','36.99','55.99','37.99','56.99','38.99','57.99','38.99','58.99','39.99','59.99','40.99','60.99','40.99','61.99','41.99','62.99','42.99','63.99','42.99','64.99','43.99','65.99','44.99','66.99','44.99','67.99','45.99','68.99','46.99','69.99','46.99','70.99','47.99','71.99','48.99','72.99','49.99','73.99','49.99','74.99','50.99','75.99','51.99','76.99','51.99','77.99','52.99','78.99','53.99','79.99','53.99','80.99','54.99','81.99','55.99','75.00','50.99','84.99','57.99','100.00','67.99','125.00','84.99',""),IF(AND({US Price (Printed)}!="",{US Price (Printed)}<'10.98',{Format Type}="Paperback"),{US Price (Printed)}-'2.00',IF(AND({US Price (Printed)}!="",{US Price (Printed)}>'10.98',{Format Type}="Paperback"),SWITCH({US Price (Printed)},'10.99','7.99','11.99','8.99','12.99','9.99','13.99','10.99','14.99','10.99','15.99','11.99','16.99','12.99','17.99','12.99','18.99','13.99','19.99','14.99','20.99','15.99','21.99','15.99','22.99','16.99','23.99','17.99','24.99','18.99','25.99','18.99','26.99','19.99','27.99','20.99','28.99','20.99','29.99','21.99','30.99','22.99','31.99','23.99','32.99','23.99','33.99','24.99','34.99','25.99','35.99','25.99','36.99','26.99','37.99','27.99','38.99','28.99','39.99','28.99','40.99','29.99','41.99','30.99','42.99','31.99','43.99','31.99','44.99','32.99','45.99','33.99','46.99','33.99','47.99','34.99','48.99','35.99','49.99','36.99','50.99','36.99','51.99','37.99','52.99','38.99','53.99','38.99','54.99','39.99','55.99','40.99','56.99','41.99','57.99','41.99','58.99','42.99','59.99','43.99','60.99','43.99','61.99','44.99','62.99','45.99','63.99','46.99','64.99','46.99','65.99','47.99','66.99','48.99','67.99','49.99','68.99','49.99','69.99','50.99','70.99','51.99','71.99','51.99','72.99','52.99','73.99','53.99','74.99','54.99','75.99','54.99','76.99','55.99','77.99','56.99','78.99','56.99','79.99','57.99','80.99','58.99','75.00','54.99','84.99','61.99','100.00','72.99','125.00','90.99',""),IF(AND({US Price (Printed)}!="",{US Price (Printed)}<'10.98',{Format Type}="Issue"),{US Price (Printed)}-'1.00',IF(AND({US Price (Printed)}!="",{US Price (Printed)}>'10.98',{Format Type}="Issue"),SWITCH({US Price Printed)},'10.99','7.99','11.99','8.99','12.99','9.99','13.99','10.99','14.99','10.99','15.99','11.99','16.99','12.99','17.99','12.99','18.99','13.99','19.99','14.99','20.99','15.99','21.99','15.99','22.99','16.99','23.99','17.99','24.99','18.99','25.99','18.99','26.99','19.99','27.99','20.99','28.99','20.99','29.99','21.99','30.99','22.99','31.99','23.99','32.99','23.99','33.99','24.99','34.99','25.99','35.99','25.99','36.99','26.99','37.99','27.99','38.99','28.99','39.99','28.99','40.99','29.99','41.99','30.99','42.99','31.99','43.99','31.99','44.99','32.99','45.99','33.99','46.99','33.99','47.99','34.99','48.99','35.99','49.99','36.99','50.99','36.99','51.99','37.99','52.99','38.99','53.99','38.99','54.99','39.99','55.99','40.99','56.99','41.99','57.99','41.99','58.99','42.99','59.99','43.99','60.99','43.99','61.99','44.99','62.99','45.99','63.99','46.99','64.99','46.99','65.99','47.99','66.99','48.99','67.99','49.99','68.99','49.99','69.99','50.99','70.99','51.99','71.99','51.99','72.99','52.99','73.99','53.99','74.99','54.99','75.99','54.99','76.99','55.99','77.99','56.99','78.99','56.99','79.99','57.99','80.99','58.99','75.00','54.99','84.99','61.99','100.00','72.99','125.00','90.99',""))))))))
We don’t really have issues that’ll be more than 10.99, but they’d fall on the same grid as Paperback if we do. The items under 10.99 are calculating correctly, but the SWITCH clauses don’t appear to be doing anything, and given how many values there are, I hope there’s a solution!
