Remember Me? December 14th 08, 07:36 AM posted to microsoft.public.excel.worksheet.functions
 Steven Masterson external usenet poster First recorded activity by ExcelBanter: Dec 2008 Posts: 1 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!! December 14th 08, 09:24 AM posted to microsoft.public.excel.worksheet.functions
 Max external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221 How do I perform calculation within IF statement?

=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
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!! December 14th 08, 09:58 AM posted to microsoft.public.excel.worksheet.functions
 David Biddulph[_2_] external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651 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
...
=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
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
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!! December 14th 08, 10:48 PM posted to microsoft.public.excel.worksheet.functions
 Dana DeLouis[_3_] external usenet poster First recorded activity by ExcelBanter: Nov 2008 Posts: 690 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!! December 15th 08, 08:13 AM posted to microsoft.public.excel.worksheet.functions
 Max external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221 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
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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Pat Adams Excel Worksheet Functions 1 July 7th 08 11:34 PM vjaggers Excel Worksheet Functions 0 June 5th 08 03:24 PM Eric Excel Discussion (Misc queries) 1 May 28th 07 02:15 AM dazp1970 Excel Worksheet Functions 2 September 10th 05 01:17 PM julieskennels Excel Worksheet Functions 7 July 18th 05 08:07 PM

All times are GMT +1. The time now is 07:13 PM. Copyright ©2004-2021 ExcelBanter.