Equation needed

[Sara_Abukhadra]

Hi everyone,

Need some help here.

I am part of an ecommerce site for second hand luxury items. I need some help with an equation to calculate the value sellers receive for their products. I am guessing will need to use the IF Function. The price is being split up.

Here is a breakdown of what a seller will receive.
On the first AED 400, you will receive 40%of the selling price.
On anything between AED 400 – AED 8000, you will receive 65%of the selling price
On anything exceeding AED 8000, you will receive 80% of the selling price.

Here is an example for you. If you are selling a handbag at AED 10,000 this is what you will get:
First AED 400- you will receive 40% of the value, that is AED160
For balance AED 8,000between AED400 – AED8,000 – you will receive, 65% of the selling price, that is AED 5,200
For balance AED 1,600 exceeding 2000 – you will receive 80% of the value, that is AED 1,280

Could you help me formulate this on airtable?
Thank you so much

[Elias_Gomez_Sai]

You just need the IF() function (2 concatenated times) and to multiply for the percentage.

• Logical functions (IF)
• Nested IF formulas

[Andy_Lin1]

@Sara_Abukhadra I’m afraid I don’t quite understand the math, was there a typo?

Given
AED 0–400 * 0.4;
AED 400–8,000 * 0.65
AED 8,000+ * 0.8

Shouldn’t a AED 10,000 handbag be broken down as follows?
AED 0–400 = 400 * 0.4 = 160
AED 400–8,000 = 7,600 * 0.65 = 4,940
AED 8,000–10,000 * 0.8 = 2,000 * 0.8 = 1,600

If the above is correct, then the following formula should work for you:

=IF( {PRICE} <= 400 ,
	{PRICE} * 0.4 ,
	IF( {PRICE} > 8000 ,
		( {PRICE} - 8000 ) * 0.8 + 4940 + 160 ,
		( {PRICE} - 400 ) * 0.65 + 160 
	)
)

If you want something without hard-coded numbers, you can use:

=MIN( {PRICE} , 400 ) * 0.4 +
MAX( MIN( {PRICE} , 8000 ) - 400 , 0 ) * 0.65 + 
MAX( {PRICE} - 8000 , 0 ) * 0.8

Note however that MIN/MAX will behave strangely with blank cells.