How do I perform calculation within IF statement?
 

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(A11000,=((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!!

How do I perform calculation within IF statement?
 

Ref your attempt in A2:
=IF(A1<25,=(A1/100)*8.75,IF(A1<1000,=((A1-25)/100)*3.5+2.19,IF(A11000,=((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(A125,A1<=1000),((A1-25)/100)*3.5+2.19,
IF(A11000,((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
---
"Steven Masterson" wrote:
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(A11000,=((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!!

How do I perform calculation within IF statement?
 

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

"Max" wrote in message
...
Ref your attempt in A2:
=IF(A1<25,=(A1/100)*8.75,IF(A1<1000,=((A1-25)/100)*3.5+2.19,IF(A11000,=((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(A125,A1<=1000),((A1-25)/100)*3.5+2.19,
IF(A11000,((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
---
"Steven Masterson" wrote:
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(A11000,=((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!!

How do I perform calculation within IF statement?
 

Hi. Another option...

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

= = =
HTH :)
Dana DeLouis




Steven Masterson wrote:
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(A11000,=((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!!

How do I perform calculation within IF statement?
 

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
---
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Why do you think it needs an AND, Max?
You've already tested for A1<=25, so it doesn't need the A125 test, does
it?
--
David Biddulph