Can anyone capture the following logic in a nested-IF statement so I can copy/paste straight into Excel?
£0.01 - £4.99 10% for the amount of the selling price up to £4.99
£5.00 - £9.99 10% of the initial £4.99 plus 8% of the remaining selling price balance
£10.00 - £49.99 10% of the initial £4.99 plus 8% of the initial £5.00 - £9.99 plus 6% of the remaining selling price balance
£50.00 - £499.99 10% of the initial £4.99 plus 8% of the initial £5.00 - £9.99 plus 6% of the initial £10.00 - £49.99 plus 4% of the remaining selling price balance
Over £500 10% of the initial £4.99 plus 8% of the initial £5.00 - £9.99 plus 6% of the initial £10.00 - £49.99 plus 4% of the initial £50.00 - £499.99 plus 2% of the remaining closing value balance
2006-09-04 04:31:03 · 6 answers · asked by Anonymous in Science & Mathematics ➔ Mathematics
Using C9 to represent 'selling price'
2006-09-04 04:32:12 · update #1
i don't think any of you fully understand - I am a complete imbecile when it comes to formulas, and can't do it myself. Whilst all your suggestions are probably great, I wouldnt know where to start. I'm just trying to get excel to calculate fees around eBays new pricing:
http://pages.ebay.co.uk//sell/importantinfo/
2006-09-04 04:46:20 · update #2
=0.1*MIN(C9,4.99)
+IF(C9>4.99,0.08*
(MIN(C9,9.99)-4.99)
+IF(C9>9.99,0.06*
(MIN(C9,49.99)-9.99)
+IF(C9>49.99,0.04*
(MIN(C9,499.99)-49.99)
+IF(C9>499.99,0.02*
(C9-499.99)))))
Have to write it like this as doesn't copy over to well from excel!
However, have tested it & it copies/pastes fine from Yahoo to Excel.
I hope you give me the 10 points for this!
2006-09-04 04:58:33 · answer #1 · answered by Young Man 3 · 1⤊ 0⤋
Sorry didnt get your personal message until now .. it does look like these guys have stepped in and helped you out and this combined with my earlier answer should sort it.
I would however suggest putting the various rates etc in a table as mentioned elsewhere and using the cell references in the formula rather than the prices as it makes it easier to change at a later date when Ebay invariably change the rates ..(cut and paste changing formulae is fiddly at best)
Good luck with the auctions and let me know if you need any further help.
2006-09-04 21:14:45 · answer #2 · answered by enzuigiriuk 4 · 1⤊ 0⤋
Looks like the progressive income tax in the US. I would not try to do it as a single formula or you will go crazy. Do each range in a separate cell and add the results together.
2006-09-04 04:41:59 · answer #3 · answered by Barkley Hound 7 · 0⤊ 0⤋
I could yes, but that would just be lazy on your part woudn't it ?
And I'd be doing your job when you were being paid for it, that would not be moral, would it ?
You'd be better off with tables that a long ( but simple ) formula. It'll be easier to debug in the future if the prices or % change
2006-09-04 04:42:52 · answer #4 · answered by Michael H 7 · 0⤊ 1⤋
IF(c9<5, c9*.1,
if(c9<10, .499+(c9-4.99)*.08,
if(c9<50,..899+(c9-9.99)*.06,
if(c9<500,3.9+(c9-49.99)*.04,
c9>499.99,21.9+
(c9-499.99)*.02))))
2006-09-04 05:02:15 · answer #5 · answered by kingofclubs_uk 4 · 0⤊ 1⤋
I will do the first part, then you have to do the rest
=if(a1<=4.99,a1*.1,if(a1<=9.99,4.99*.1+(a1-4.99)*.08, ...))
2006-09-04 04:38:31 · answer #6 · answered by rscanner 6 · 0⤊ 0⤋