Skip to main content

I am trying to use an IF statement and I can’t get it to work either with an AND or an OR formula (or both)…


Essentially I have a calculated price for my products and I want to create a formula field for a selection of the products IF ‘Sub-Category = X OR Y’ then take ‘Calculated Price +0.1’ to add 10p to the calculated price.


I have tried this which returns a result for all products and not just those where the Sub-Category is “Coffee Retail Beans” or “Coffee Retail Ground”.


IF(OR({Sub-Category}=“Coffee Retail Beans”, “Coffee Retail Ground”), {Calc Price}+0.1)


Any other combos I try using AND statements where I have followed other similar examples on different threads, but they always come back with a formula error.


Basically I have a list of wholesale and retail products each with a calculated price and for customers who only buy retail I want to add 10p to their price, but I want this to only apply to products with a retail ‘sub-category’. If the ‘sub-category’ isn’t a retail one then I don’t want it to leave this new price blank…


I have tried following the logic of the help pages, but I’m not getting something right on this and can’t work it out.


If anyone can help me on this it would be greatly appreciated!

If I’m understanding correctly, you’ll want a “Retail Price” field or something for the “Calc Price” field to calculate from, and this is where you’ll enter the standard prices.


If these are the only 2 “Sub-Categories” you’ll need to check for, and it’s a single select field, you could use this:


IF(OR({Sub-Category}="Coffee Retail Beans", {Sub-Category}="Coffee Retail Grounds"), {Retail Price}+.1, {Retail Price})


If you’d rather just check whether Sub-Category has the word “Retail” in it (it is case sensitive), then this should work :


IF(FIND("Retail",{Sub-Category}), {Retail Price}+.1, {Retail Price})

If I’m understanding correctly, you’ll want a “Retail Price” field or something for the “Calc Price” field to calculate from, and this is where you’ll enter the standard prices.


If these are the only 2 “Sub-Categories” you’ll need to check for, and it’s a single select field, you could use this:


IF(OR({Sub-Category}="Coffee Retail Beans", {Sub-Category}="Coffee Retail Grounds"), {Retail Price}+.1, {Retail Price})


If you’d rather just check whether Sub-Category has the word “Retail” in it (it is case sensitive), then this should work :


IF(FIND("Retail",{Sub-Category}), {Retail Price}+.1, {Retail Price})


Hi Nick, thank you for your response.


I think I’m almost there except for the sub-categories that aren’t the two retail ones, the formula returns the Retail Price (not with the +.1) where as I’m trying to get it to leave it blank for all the non retail sub-categories…


I tried changing out the end {Retail Price} with !="" but that threw the formula into an error.

Hi Nick, thank you for your response.


I think I’m almost there except for the sub-categories that aren’t the two retail ones, the formula returns the Retail Price (not with the +.1) where as I’m trying to get it to leave it blank for all the non retail sub-categories…


I tried changing out the end {Retail Price} with !="" but that threw the formula into an error.


Oh I see. In that case, you can just remove the final comma and {Retail Price} from either of those formulas. It’s just there to tell Airtable what to do if the IF statement comes back false. If you leave it blank, then it should return nothing in that case.

Oh I see. In that case, you can just remove the final comma and {Retail Price} from either of those formulas. It’s just there to tell Airtable what to do if the IF statement comes back false. If you leave it blank, then it should return nothing in that case.


Thank You Nick!


So simple once you see it but I wasn’t adding {Sub-Category} in front of each option.


You guys must literally think like computers!! thanks again for the help.

Reply


Terms & Conditions