Skip to main content

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

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


@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.

Reply


Terms & Conditions