Formula works on paper, but not in spreadsheet. Error = a specific expression is added to desired result.

[FreshlySqueezed]

Hello, l am using LibreOffice Calc but l'm hoping it won't affect matters.


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.

 

[jasonb75]

I got lost somewhere in your over-explaination, see if you can do anything with this

=$X2-MIN($X2*$Y2%,IF($AG$6>0,$AG$6))-MIN($X2*$AG$3%,$X2*$AG$7%,IF($AG$4>0,$AG$4),IF($AG$8>0,$AG$8))

This will deduct the lowest of percentage or fixed fee from the original price in X2 for both ebay and the payment gateways. In all cases, fixed fee will be ignored if it is not greater than 0.

Not sure if the % trick works with libre, so you might have to revert back to /100 or enter your figures as percentages in the cells.

 

[FreshlySqueezed]

Hi there thank you for your reply.

Your formula gives a value that looks like it should be right and l could quickly crunch some numbers but l did a simple acid test: Change eBay Fee Cap (the notorious $AG$6) from blank or zero, to £20.

The product of the formula changes even though an item is, say £29.99, and the fee rate is 9.6%. That would amount to around £2.90 in fees, so the eBay Fee Cap shouldn't change a thing.



Let me just elaborate on the example l gave in the OP:


EXAMPLE OF THE PROBLEM:
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%)
PayPal Fee Rate (%) = $AG$3 = 2.9
PayPal Fixed Fee per Transaction (£) = $AG$4 = £0.30
Cart Payment Gateway Fee Rate (%) = $AG$7 = 2.9
Cart Payment Gateway Fixed Fee per Transaction (£) = £0.20 (yes, £0.20, not £0.30 as it is for PayPal)

Area of the formula that should be triggered:
$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]

Actual Result: Basic Item Price = £527.04
Expected Result: £494.46389 [this is also what you get if you fill the above-mentioned part of the formula in with the given values in this example i.e. $X2-(($X2*($Y2/100))+($X2*($AG$7/100))+$AG$8) -----> £542.99-((£542.99*(6/100))+(£542.99*(2.9/100))+£0.20)]

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.

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 (£527.04), MINUS the eBay Fee Cap of £20.

I'm sure it's something obvious that has gone wrong, because the error itself correlates to whatever the value of the eBay Fee Cap ($AG$6) is.

 

[Snakehips]

Try...

=$X2-MIN($X2*$Y2%,IF($AG$6>0,$AG$6,9^9))-MIN($X2*$AG$3%+$AG$4,$X2*$AG$7%+$AG$8)

Hope that helps.

 

[jasonb75]

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

Not sure where you get 32.5794 from, 6% of 527.04 = 31.6224

I found a couple of errors in my formula, part of it was due to ine of the functions not doing what I had expected it to, but also because I was folloing part of your question incorrectly, I was using the fixed fee per transaction for the payment gateways as an alternative to the percentage, the same as the ebay fixed fee, instead of adding the flat rate fee to the percentage as it should be.

This revised formula,

=$X2-IF($AG$8>0,MIN($X2*$Y2%,$AG$6),$X2*$Y2%)-MIN($X2*$AG$3%+$AG$4,$X2*$AG$7%+$AG$8)

gives a result of 479.9334 for the initial amount of 527.04, which is a deduction of 31.6224 (ebay 6% fee) and alt gateway fee of 15.48416 (2.9% + 0.20) which is marginally lower than the paypal fee of 15.58416 (2.9% + 0.30).

As for why your formula isn't working as expected, probably misplaced parentheses, I try to avoid such long formulas for that very reason.

 

[FreshlySqueezed]

Try...

=$X2-MIN($X2*$Y2%,IF($AG$6>0,$AG$6,9^9))-MIN($X2*$AG$3%+$AG$4,$X2*$AG$7%+$AG$8)

Hope that helps.

Indeed it does, congratulations you've solved it!

Still, wish l knew what was wrong with my formula, but l'm happy to close the book on it anyhow.

Not sure where you get 32.5794 from, 6% of 527.04 = 31.6224

Quoting the previous post: eBay Item Price (i.e. $X2) = £542.99
6% of that = £32.5794

I found a couple of errors in my formula, part of it was due to ine of the functions not doing what I had expected it to, but also because I was folloing part of your question incorrectly, I was using the fixed fee per transaction for the payment gateways as an alternative to the percentage, the same as the ebay fixed fee, instead of adding the flat rate fee to the percentage as it should be.

This revised formula,

=$X2-IF($AG$8>0,MIN($X2*$Y2%,$AG$6),$X2*$Y2%)-MIN($X2*$AG$3%+$AG$4,$X2*$AG$7%+$AG$8)

Congratulations, you've solved it too, in a different way to Snakehips!

As for why your formula isn't working as expected, probably misplaced parentheses, I try to avoid such long formulas for that very reason.

I did wonder that. But in my lengthy intro, l broke the formula down to reveal the symmetry in parentheses l hope.

Also, l checked it over and over for the placing of parentheses within each unbroken section. It all seemed fine. The error was that stuff was being added twice, and stuff was being subtracted as it should, but from an underlying error where stuff had been added twice. But following the parentheses, that just should not have happened.

It would be really interesting to know what my original error was.

I wonder if my original formula would actually work in Excel rather than LibreOffice Calc where it has clearly failed? I may give it a go. But anyway thanks guys!

 

[jasonb75]

To be honest I didn't even try to disect your formula, but a quick evaluation has highlighted one problem.

X2=542.99, Y2=6, AG3=2.9, AG4=0.30, AG7=2.9, AG8=0.20, all other cells empty.

In your formula, the first AND returns TRUE on all logical tests, so only the first calculation is carried out (red bit below). There is no calculation included there to deduct the ebay fee from X2, so rather than adding it on as you assumed, it was never decucted in the first place.

=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)

As everything else falls under the FALSE argument of the first IF, none of the calculations there will ever be carried out. This is just from evaluating the first section, I suspect that there could be similar issues with other sections of the formula depending on how the results of the logical tests pan out.

To make your method work, you would need to look at something like =$X2-IF(AND(...),true,false)-IF(AND(...),true,false) rather than $X2-IF(AND(...),true,IF(AND(...),true,false))
Note that in the first example above, the first IF is closed before the second is opened, it is not nested inside the first one like you have done with your formula.

 

[FreshlySqueezed]

Hi there thanks,

My intention was: First element = There's an eBay fee cap. The fee cap has been exceeded by the actual eBay fees, so fee cap is triggered. Also, PayPal fees are greater than Alternative Payment Gateway fees.

Then, nested: Alternatively, if the above applies except that Alt Gateway fees are greater than Paypal.

Then so on and so forth.


I honestly had NO idea your syntax was correct ---> =$X2-IF(AND(...),true,false)-IF(AND(...),true,false) rather than $X2-IF(AND(...),true,IF(AND(...),true,false))
I must be behind the times but l always thought nested IFs were the proper way and all else would result in blank response, zero, or error message?

I must be behind the times!