Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 14th 08, 07:36 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 1
Default 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!!

  #2   Report Post  
Old December 14th 08, 09:24 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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!!

  #3   Report Post  
Old December 14th 08, 09:58 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default 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!!



  #4   Report Post  
Old December 14th 08, 10:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 690
Default 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!!

  #5   Report Post  
Old December 15th 08, 08:13 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Perform a calculation on same cell across many worksheets Pat Adams Excel Worksheet Functions 1 July 7th 08 11:34 PM
perform a calculation if checkbox marked vjaggers Excel Worksheet Functions 0 June 5th 08 03:24 PM
How to perform matrix calculation in excel? Eric Excel Discussion (Misc queries) 1 May 28th 07 02:15 AM
comparing two columns & then perform a calculation dazp1970 Excel Worksheet Functions 2 September 10th 05 01:17 PM
perform calculation only if current time is after 10 am julieskennels Excel Worksheet Functions 7 July 18th 05 08:07 PM


All times are GMT +1. The time now is 05:14 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017