How do I perform calculation within IF statement?

[Steven Masterson]

Hello,

I'm trying to create a worksheet that will calculate all the fees eBay
charges for an item when I enter the sold price. I've created a basic spread
sheet as follow:
A1:
1850
A2
=IF(A1<25,=(A1/100)*8.75,IF(A1<1000,=((A1-25)/100)*3.5+2.19,IF(A1>1000,=((A1-1000)/100)*1.5+2.19+34.12,"Incorrect Sold Price")))

ebay will charge you a final value fee based on how much your item is sold
for, the calculation is as below:
$0.01~$25.00=8.75% of the closing value

$25.01~1,000=8.75% of the initial $25.00 (2.19), plus 3.5% of the remaining
closing value balance

Equal to or Over $1000=8.75% of the initial $25.00 ($2.19), plus 3.5% of the
initial $25.01~$1,000 ($34.12), Plus 1.5% of the remaining closing value
balance

So what I was trying to achieve with the formula above is basically using
the IF statement to verify one of the three condition, where A1 is the final
value for the item, this formula in the A2 field should see which price range
A1 falls into, and apply the correct calculation to display result.
(You will probably think I'm stupid after you realize I was dividing A1
field by 100 and times a certain number just because I don't know how to
properly do percentage within Excel formula...)

I've also tried using LOOKUP function, but doesn't matter how I modify it,
it will either tell me there's an error or will simply spit out the entire
line of formula instead of a calculated result.

Any help is really appreciated, thanks a lot!!

 

[Max]

Ref your attempt in A2:

=IF(A1<25,=(A1/100)*8.75,IF(A1<1000,=((A1-25)/100)*3.5+2.19,IF(A1>1000,=((A1-1000)/100)*1.5+2.19+34.12,"Incorrect Sold Price")))

Try a direct copy of the tweaked formula below,
then paste directly into the formula bar for A2:

=
IF(A1<=25,(A1/100)*8.75,
IF(AND(A1>25,A1<=1000),((A1-25)/100)*3.5+2.19,
IF(A1>1000,((A1-1000)/100)*1.5+2.19+34.12,
"Incorrect Sold Price")))

The middle IF term should house an AND construct, and I've also tweaked the
limits a little so that you have no gaps. Test it out and re-tweak to suit (I
didn't touch the resultant calcs for the 3 tiers)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---

 

[David Biddulph]

Why do you think it needs an AND, Max?
You've already tested for A1<=25, so it doesn't need the A1>25 test, does
it?

 

[Dana DeLouis]

Hi. Another option...

= Min(0.0875*A1, 1.3125 + 0.035*A1, 21.3125 + 0.015*A1)

= = =
HTH :>)
Dana DeLouis

 

[Max]

You're right. Guess I got carried away with trying to inject too much
clarity on the limits testing, David.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik