Home 
Search 
Today's 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,=((A125)/100)*3.5+2.19,IF(A11000,=((A11000)/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




How do I perform calculation within IF statement?
Ref your attempt in A2:
=IF(A1<25,=(A1/100)*8.75,IF(A1<1000,=((A125)/100)*3.5+2.19,IF(A11000,=((A11000)/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),((A125)/100)*3.5+2.19, IF(A11000,((A11000)/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 retweak 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,=((A125)/100)*3.5+2.19,IF(A11000,=((A11000)/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




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,=((A125)/100)*3.5+2.19,IF(A11000,=((A11000)/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),((A125)/100)*3.5+2.19, IF(A11000,((A11000)/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 retweak 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,=((A125)/100)*3.5+2.19,IF(A11000,=((A11000)/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




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,=((A125)/100)*3.5+2.19,IF(A11000,=((A11000)/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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Perform a calculation on same cell across many worksheets  Excel Worksheet Functions  
perform a calculation if checkbox marked  Excel Worksheet Functions  
How to perform matrix calculation in excel?  Excel Discussion (Misc queries)  
comparing two columns & then perform a calculation  Excel Worksheet Functions  
perform calculation only if current time is after 10 am  Excel Worksheet Functions 