Formula displaying incorrect result

Topic Labels: Formulas
1162 6
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

Screenshot 2021-09-12 173843
As you can see in the image, formula (price - discount + shipping charges) is returning 1 in first field as I have two values separated by commas that is 399,1076.
Is there any way to write this formula, so it takes into account units separated by commas and return the correct result.

6 Replies 6
10 - Mercury
10 - Mercury

Hi Dhaval. Assuming you want to sum all the prices in the “Price” field before subtracting discount and adding shipping, I would use a “rollup” field, instead, that summed those prices for you.

But I want Prices after deducting Discount.
To give a better idea refer image in which I have not used any formulas and have entered figures manually. So, let’s say price of one product is 399 and discount is 50, & another product’s price is 1076 and discount is 40. So I want result of (399-50) & (1076-40) i.e 349,1036. I want exact same results when I enter formula so that I don’t have to do calculation manually for the every record I add. Screenshot 2021-09-12 185559

Oh, I see. You want to end up with an array of invoice prices derived from other arrays. If the unit discount and the shipping is specific to each customer, then I really don’t know how to do this in Airtable. Hopefully, someone will but there’s not much array magic available in formulas.

Math with lookup fields does not work the way you want.

You should use a junction table where you enter the discount for each item in the sale and calculate the discounted price. Then you can rollup all the discounted prices.

I could do that but the real challenge is that I have listed multiple coupon codes on multiple e-commerce platforms (amazon, Noon, etc), so there is not specific discount code or rates for products. Additionally, I also sell my products on my website at full prices (no discount). So there would be a myriad types of invoices from where I’d be recording discounts. It could be 0, 50, 10, anything.

I assume that you could use an automation, triggered on specific field updates, to run a script that iterate/calc through the arrays.