Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Between calculations
Good day. I am using Excel 2002. I have a need to
calculate shipping charges based on four parameters. The table is as follows; 000.00 - 249.99 = 7.00 250.00 - 499.99 = 10.00 500.00 - 999.99 = 12.00 1000.00 and up = 15.00 There will be one cell that will hold the total product cost. If necessary, a separate worksheet can be generated to hold a table for the four parameters. I have tried several combinations and don't seem to be getting the right one. Please advise. Thanks, Danno... |
#2
|
|||
|
|||
"Dan Wilson" wrote in message ... Good day. I am using Excel 2002. I have a need to calculate shipping charges based on four parameters. The table is as follows; 000.00 - 249.99 = 7.00 250.00 - 499.99 = 10.00 500.00 - 999.99 = 12.00 1000.00 and up = 15.00 There will be one cell that will hold the total product cost. If necessary, a separate worksheet can be generated to hold a table for the four parameters. I have tried several combinations and don't seem to be getting the right one. Please advise. Thanks, Danno... I'm not exactly certain how to do the actual math of it, but I think what you are looking for are nested functions, basically the first one asks is x greater than 1000, y/n... if y then value z is 15, if not then is value x greater than 500? y/n, is yes then value z = 12, if no .. and so on... |
#3
|
|||
|
|||
Hi!
Here's one way: Assume cell A1 is the total cost. =IF(A1="","",LOOKUP(A1,{0,250,500,1000},{7,10,12,1 5})) Format the cell as NUMBER 2 decimal places. Biff -----Original Message----- Good day. I am using Excel 2002. I have a need to calculate shipping charges based on four parameters. The table is as follows; 000.00 - 249.99 = 7.00 250.00 - 499.99 = 10.00 500.00 - 999.99 = 12.00 1000.00 and up = 15.00 There will be one cell that will hold the total product cost. If necessary, a separate worksheet can be generated to hold a table for the four parameters. I have tried several combinations and don't seem to be getting the right one. Please advise. Thanks, Danno... . |
#4
|
|||
|
|||
Dan,
With your data (weights?) in E6 try =IF(ISNUMBER(E6),SUM((E6={250,500,1000})*3)+(E6<5 00)+6,"") to give the appropriate 7, 10, 12 or 15 If it is to be part of the shipping charge calculation then use: =IF(ISNUMBER(E6),SUM((E6={250,500,1000})*3)+(E6<5 00)+6,0)* <Your charge HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Dan Wilson" wrote in message ... Good day. I am using Excel 2002. I have a need to calculate shipping charges based on four parameters. The table is as follows; 000.00 - 249.99 = 7.00 250.00 - 499.99 = 10.00 500.00 - 999.99 = 12.00 1000.00 and up = 15.00 There will be one cell that will hold the total product cost. If necessary, a separate worksheet can be generated to hold a table for the four parameters. I have tried several combinations and don't seem to be getting the right one. Please advise. Thanks, Danno... |
#5
|
|||
|
|||
"Biff" wrote in message ... Hi! Here's one way: Assume cell A1 is the total cost. =IF(A1="","",LOOKUP(A1,{0,250,500,1000},{7,10,12,1 5})) Format the cell as NUMBER 2 decimal places. Biff Where did that post come from? It wasn't there when I looked 10 minutes ago <g Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk |
#6
|
|||
|
|||
Good day Sandy. All I can say is WOW!!! I tried your
formula in my worksheet and it works! That is not because I am surprised, but because I thought I was good at math and formulas. You have just set me back several steps. I will be sending quite a bit of time evaluating this formula to try and understand how it works. Thanks again, Danno... -----Original Message----- Dan, With your data (weights?) in E6 try =IF(ISNUMBER(E6),SUM((E6={250,500,1000})*3)+(E6< 500) +6,"") to give the appropriate 7, 10, 12 or 15 If it is to be part of the shipping charge calculation then use: =IF(ISNUMBER(E6),SUM((E6={250,500,1000})*3)+(E6< 500)+6,0) * <Your charge HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Dan Wilson" wrote in message ... Good day. I am using Excel 2002. I have a need to calculate shipping charges based on four parameters. The table is as follows; 000.00 - 249.99 = 7.00 250.00 - 499.99 = 10.00 500.00 - 999.99 = 12.00 1000.00 and up = 15.00 There will be one cell that will hold the total product cost. If necessary, a separate worksheet can be generated to hold a table for the four parameters. I have tried several combinations and don't seem to be getting the right one. Please advise. Thanks, Danno... . |
#7
|
|||
|
|||
Dan,
It may be a good exercise but I like Biff'solution better. If I had seen it before I posted I would never have posted. Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Dan Wilson" wrote in message ... Good day Sandy. All I can say is WOW!!! I tried your formula in my worksheet and it works! That is not because I am surprised, but because I thought I was good at math and formulas. You have just set me back several steps. I will be sending quite a bit of time evaluating this formula to try and understand how it works. Thanks again, Danno... -----Original Message----- Dan, With your data (weights?) in E6 try =IF(ISNUMBER(E6),SUM((E6={250,500,1000})*3)+(E6< 500) +6,"") to give the appropriate 7, 10, 12 or 15 If it is to be part of the shipping charge calculation then use: =IF(ISNUMBER(E6),SUM((E6={250,500,1000})*3)+(E6< 500)+6,0) * <Your charge HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Dan Wilson" wrote in message ... Good day. I am using Excel 2002. I have a need to calculate shipping charges based on four parameters. The table is as follows; 000.00 - 249.99 = 7.00 250.00 - 499.99 = 10.00 500.00 - 999.99 = 12.00 1000.00 and up = 15.00 There will be one cell that will hold the total product cost. If necessary, a separate worksheet can be generated to hold a table for the four parameters. I have tried several combinations and don't seem to be getting the right one. Please advise. Thanks, Danno... . |
#8
|
|||
|
|||
Just some more fun options:
=3*(A1=1000)+2*(A1=500)+3*(A1=250)+7*(A10) Usually shorter the other way around: =15 -3*(A1<1000)-2*(A1<500)-3*(A1<250) Another variation along Dan's excellent formula: =MOD(2922,9+SUM((A1={0,250,500,1000})*2)) -- Dana DeLouis Win XP & Office 2003 "Dan Wilson" wrote in message ... Good day Sandy. All I can say is WOW!!! I tried your formula in my worksheet and it works! That is not because I am surprised, but because I thought I was good at math and formulas. You have just set me back several steps. I will be sending quite a bit of time evaluating this formula to try and understand how it works. Thanks again, Danno... -----Original Message----- Dan, With your data (weights?) in E6 try =IF(ISNUMBER(E6),SUM((E6={250,500,1000})*3)+(E6 <500) +6,"") to give the appropriate 7, 10, 12 or 15 If it is to be part of the shipping charge calculation then use: =IF(ISNUMBER(E6),SUM((E6={250,500,1000})*3)+(E6 <500)+6,0) * <Your charge HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Dan Wilson" wrote in message ... Good day. I am using Excel 2002. I have a need to calculate shipping charges based on four parameters. The table is as follows; 000.00 - 249.99 = 7.00 250.00 - 499.99 = 10.00 500.00 - 999.99 = 12.00 1000.00 and up = 15.00 There will be one cell that will hold the total product cost. If necessary, a separate worksheet can be generated to hold a table for the four parameters. I have tried several combinations and don't seem to be getting the right one. Please advise. Thanks, Danno... . |
#9
|
|||
|
|||
"Dana DeLouis" wrote in message
... Just some more fun options: Another variation along Dan's excellent formula: =MOD(2922,9+SUM((A1={0,250,500,1000})*2)) I see it but I don't believe it <g Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk |
#10
|
|||
|
|||
Hi Sandy. This isn't to useful here, but you might be interested in another
option. I can never do this from inspection of the data. If you treat the data table as a Signal pulse, and take the Inverse Fourier Transform, you arrive at the following suggestion: =(1/2)*(3*SIGN(A1-999.999)+2*SIGN(A1-499.999)+3*SIGN(A1-249.999)+22) Do to the nature of the data ranges, a small delta time delay is added. The reason I mention it is that in VBA, the following equation: ans=(1/2)*(3*Sgn(t-999.999)+2*Sgn(t-499.999)+3*Sgn(t-249.999)+22) is about 52 times faster then this equation (based on my timing tests): ans=WorksheetFunction.Lookup(t,Array(0,250,500,100 0),Array(7,10,12,15)) Again, just thought you might find it interesting. :) -- Dana DeLouis Win XP & Office 2003 "Sandy Mann" wrote in message ... "Dana DeLouis" wrote in message ... Just some more fun options: Another variation along Dan's excellent formula: =MOD(2922,9+SUM((A1={0,250,500,1000})*2)) I see it but I don't believe it <g Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk |
#11
|
|||
|
|||
"Dana DeLouis" wrote in message
... Hi Sandy. This isn't to useful here, but you might be interested in another option. I can never do this from inspection of the data. If you treat the data table as a Signal pulse, and take the Inverse Fourier Transform, you arrive at the following suggestion: =(1/2)*(3*SIGN(A1-999.999)+2*SIGN(A1-499.999)+3*SIGN(A1-249.999)+22) Oh! come on Dana I'm a simple bicycle repair man - although it is true that at school I was compared to Einstein - the teacher said to my parents, "Well he's no Einstein!" Re-arranging your truly excellent formula gives: =SIGN(A1-499.999)+1.5*(SIGN(A1-249.999)+SIGN(A1-999.999))+11 which still seems to work, at least like your original, for whole numbers. Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating calculations + graph when i enter in new data WITHOUT OFF | Excel Worksheet Functions | |||
calculations from data in other worksheets | Excel Worksheet Functions | |||
Format a worksheet to carry out all calculations to 2 decimal plac | Excel Discussion (Misc queries) | |||
Format a worksheet to carry out all calculations to 2 decimal plac | Excel Discussion (Misc queries) | |||
How can I format a worksheet to carry out all calculations to 2 d. | Excel Discussion (Misc queries) |