#### FreshlySqueezed

##### New Member

- Joined
- Sep 22, 2019

- Messages
- 10

KEY TO EXPRESSIONS:

$AG$3 = PayPal fee percentage

$AG$4 = PayPal fixed fee per transaction (e.g. £0.30 per transaction)

$AG$6 = eBay Fee Cap

$AG$7 = Alternative Payment Gateway fee percentage

$AG$8 = Alternative Payment Gateway fixed fee per transaction (e.g. £0.30 per transaction)

Column X = eBay Price, this column has one row per item, my example relates to the first row, row 2, i.e. $X2

Column Y = eBay Fee Rate for that specific item. This column has one row per item, my example relates to the first row, row 2, i.e. $Y2

Here is the problem explained:

I am trying to calculate what l call the "Basic Item Price", that is, the eBay price minus all fees, or another way to see it: the cash-in-hand direct sale price, with no eBay fees, no payment processing fees.

I therefore look at which payment processing fees are the highest: PayPal or the Alternative Payment Gateway that l have set up?

Pursuant to this, l then use the gateway (PayPal or the Alternative) that gives the lowest fee (okay, l should have done that part of the formula better, i.e. calculate which gateway is lowest not highest).

From the eBay price, l subtract the eBay fees, and then l subtract the payment gateway that has the lowest fees.

I therefore arrive at the highest possible "Basic Item Price" and this is the price l shall use.

There is one caveat though: sometimes eBay may cap their fees for a particular item category. This is important because for an expensive item, the eBay fees might otherwise be quite high, and so my Basic Item Price will be quite low once the full eBay fees are subtracted.

BUT if eBay have capped their fees, this allows me to subtract less from my eBay Item Price to give the Basic Item Price (i.e. l therefore subtract only the capped eBay fee along with payment processing fees, rather than the otherwise massive eBay fee plus payment processing fees).

The eBay Fee Cap is cell $AG$6 and it is messing everything up.

To get the "Basic Item Price", l used this problematic formula:

=IF(AND($AG$6>=0,$X2*($Y2/100)>$AG$6,($X2*($AG$3/100))+$AG$4>($X2*($AG$7/100))+$AG$8),$X2-($AG$6+($X2*($AG$7/100))+$AG$8),IF(AND($AG$6>=0,$X2*($Y2/100)>$AG$6,($X2*($AG$3/100))+$AG$4<=($X2*($AG$7/100))+$AG$8),$X2-($AG$6+($X2*($AG$3/100))+$AG$4),IF(($X2*($AG$3/100))+$AG$4>($X2*($AG$7/100))+$AG$8,$X2-(($X2*($Y2/100))+($X2*($AG$7/100))+$AG$8),$X2-(($X2*($Y2/100))+($X2*($AG$3/100))+$AG$4))))

Explanation of the formula:

=IF(AND

(

$AG$6>=0,$X2*($Y2/100)>$AG$6,

($X2*($AG$3/100))+$AG$4>($X2*($AG$7/100))+$AG$8

),

[EXPLANATION: If eBay Fee Cap is set i.e. if it is a number ****AND**** is exceeded, ****AND**** the PayPal payment processing fees are greater than the alternative payment gateway fees]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--->THEN:

$X2-($AG$6+($X2*($AG$7/100))+$AG$8),

[EXPLANATION: Then output the eBay Item Price minus eBay Fee Cap minus Alternative Payment Gateway fees]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF(AND

(

$AG$6>=0,$X2*($Y2/100)>$AG$6,($X2*($AG$3/100))+$AG$4<=($X2*($AG$7/100))+$AG$8

),

[EXPLANATION: If eBay Fee Cap is set i.e. if it is a number ****AND**** is exceeded, ****AND**** the PayPal payment processing fees are less than / equal to the alternative payment gateway fees]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---> THEN:

$X2-($AG$6+($X2*($AG$3/100))+$AG$4),

[EXPLANATION: Then output the eBay Item Price minus eBay Fee Cap minus PayPal payment processing fees]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---> OTHERWISE, IF (i.e. WE ASSUME THAT THE EBAY FEE CAP IS BLANK, OR ZERO - THE ALTERNATIVES WERE COVERED BY THE OPENING TWO IF STATEMENTS OF THE FORMULA):

IF(

($X2*($AG$3/100))+$AG$4>($X2*($AG$7/100))+$AG$8,

[EXPLANATION: If the PayPal payment processing fees are greater than the Alternative Payment Gateway fee]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---> THEN:

$X2-(($X2*($Y2/100))+($X2*($AG$7/100))+$AG$8),

[EXPLANATION: Then output the eBay Item Price minus the full uncapped eBay fee minus the Alternative Payment Gateway fee]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---> OTHERWISE, FINALLY:

$X2-(($X2*($Y2/100))+($X2*($AG$3/100))+$AG$4)

[EXPLANATION: Otherwise output the eBay Item Price minus the full uncapped eBay fee minus the PayPal payment processing fee]

)

)

)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

THE PROBLEM EXPLAINED:

eBay Fee Cap (i.e. $AG$6) = {left blank} or 0, same result either way

eBay Item Price (i.e. $X2) = £542.99

eBay Fee Rate (i.e. $Y2) = 6 (the formula turns this into 6/100 i.e. 6%)

Result: Basic Item Price = £527.04

Expected Result: £494.46389

Funny thing: eBay Item Price*0.06 (i.e. 6/100, 6%) = £32.5794

494.46389+32.5794=527.04329

= £527.04.

Therefore it seems that the formula l devised is adding (eBay Item Price * eBay Fee Rate) to the EXPECTED result.

I calculated the formula manually step by step. The result was indeed £494.46389.

So what am l doing wrong?

Also, when l increase eBay Fee Cap (i.e. $AG$6) to £20, the Basic Item Price becomes £507.04, i.e. it becomes the Basic Item Price that the formula wrongly gives for eBay Fee Cap of zero, MINUS the eBay Fee Cap of £20.

The Fee Cap was never meant to be added to the higher uncapped estimate, but that is precisely what is happening here.

Furthermore, when l increased the eBay Fee Cap to £20, all other Basic Item Price entries for other eBay stock, also changed, even though they were very low priced and so the resulting eBay fee amounts wouldn't have been more than £1.50 or something. Nowhere near £20. Those Basic Item Price entries should not have been affected by my raising the eBay Fee Cap to £20.