Jun 26, 2018 12:38 AM
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
Jun 26, 2018 06:50 AM
You just need the IF()
function (2 concatenated times) and to multiply for the percentage.
Jun 26, 2018 03:46 PM
@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.