Results 1 to 15 of 22

20040901, 09:04 #1
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
formula / calculation problem (Excel 2000)
I am new to calculations and I suspect I may be asking too much of Excel, could anybody please tell me if what I am doing is (a) possible and ([img]/forums/images/smilies/cool.gif[/img] if I am approaching it in the right way.
I would like to publish a tariff in Excel for some staff members my fields would be : Destination  Minimum  50kilos  +50 Kilos  +100 kilos  +300kilos and so on and then finally a cell for the weight of that shipment to be entered. Each destinations rates will be different and each set of rates will be different but I think I can over come that, howeverI try to write my formula it says something about a circular query.
For simplicity I first tried (ignore whether the cell letters are right for now) =IF(J2<23,K2=J2*C2,""") eventually I will need it to say if the weight in J2 is <23 then K2 = B2 [which is the minimum].
In a nutshell can I write a multiple calculation into one cell that can tell the difference between my weight bands, this formula is obviousely wrong otherwise I would not be asking but is this possible
=IF(J2<23,K2=B2,""),IF(J2,>23<96,K2=J2*C2,""),IF(J 2>96<285,K2=J2*D2,"") I do hope this makes some sort of sense to somebody.
TIA
Steve

20040901, 09:31 #2
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: formula / calculation problem (Excel 2000)
Studying up your formula, I assume you are entering it into K2.
Try this editted version: =IF(J2<24,B2,IF(J2>23,J2*C2,IF(J2>96,J2*D2,"")))
Your formula had some syntax errors that i cleared out!
About Circular References; they occur if you create a formula say in A1, and you refer to cell A1 in the formula. Aformula cannot refer to the cell containing the formula. This creates a circular reference!
Hope this helps!Regards,
Rudi

20040901, 09:32 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: formula / calculation problem (Excel 2000)
You would put this in cell K2:
=IF(J2<23,B2,IF(and(J2>23, j2<96),J2*C2,IF(and(J2>96,j2<285),J2*D2,"")))
You circular reference is because you have D2 the cell reference in the cell. Note: If j2 = 23 or J2 = 96 or J2>=285 it will yield a null string (""), so you might want to adjust if that is not correct. (perhaps you want <=23 or >=23, etc)
You will be limited to 7 IFs. so you might have to rethink. Using some type of Lookup can accomplish the same thing.
John Walkenbach on his site gives some alternatives to these nested IFs
Post back if you have further questions.
Steve

20040901, 09:32 #4
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,120
 Thanks
 2
 Thanked 439 Times in 362 Posts
Re: formula / calculation problem (Excel 2000)
Hi Steve,
Check out the thread starting at <post#=403669>post 403669</post#>. It discusses various ways to address a related problem.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20040901, 09:37 #5
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula / calculation problem (Excel 2000)
Thanks everybody I will start working on that, also the reference to the post with the DLookup, I did download that and enabled macros but it did not seem to do anything.
Cheers again
Steve

20040901, 09:37 #6
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: formula / calculation problem (Excel 2000)
Here is a reworked example if you need to evaluate between 2 specific ranges:
=IF(J2<24,B2,IF(AND(J2>23,J2<96),J2*C2,IF(AND(J2>9 6,J2<285),J2*D2,"")))
Enter it into cell K2Regards,
Rudi

20040901, 13:59 #7
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula / calculation problem (Excel 2000)
Thanks
The answer from Steve 403774 seemed the easiest to use given my limited knowledge / ability and it works very well.
Thanks a lot for all your answers
Steve

20040902, 08:22 #8
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula / calculation problem (Excel 2000)
Hi Steve, sorry its me again
I have tried using your formula in one of my workbooks but I keep getting eroneous answers, would you mind awfully looking at my attached example to see why the calculations do not seem to behave as I believe they should.
I also in the example tried another method from another poster (403781) by using the < and > operators, in row 6 of my example I tried the following (Excel would not let me save an incorrect formula therefore I have duplicated it here).
=IF,I6<16,I6,B6,IF(AND(I6>15,I6<499,I6*C6,IF(AND(I 6>500,I6<999,I6*G6,IF(AND(I6>1000,I6<100000,I6*H6, ""))))))
But it does not seem to want to know
TIA
Steve

20040902, 08:59 #9
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: formula / calculation problem (Excel 2000)
Hi Stephen,
It was just a matter of elimination.
The formula started evaluating "IF(I2>23..... any other value incl. 101 was higher than 23, thus the formula stopped all other evaluations.
Try this formula. I simply changed the values so it starts on the highest and evaluates down to smaller values.
Copy this into cell J2:
=IF(I2<24,B2,IF(I2>1000,I2*H2,IF(I2>500,I2*G2,IF(I 2>300,I2*F2,IF(I2>100,I2*E2,IF(I2>23,I2*C2,""))))) )Regards,
Rudi

20040902, 09:14 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: formula / calculation problem (Excel 2000)
try this:
=IF(I2<24,B2,IF(I2<100,I2*C2,IF(I2<300,I2*E2,IF(I2 <500,I2*F2,IF(I2<1000,I2*G2,I2*H2)))))
Look at your eqn:
=IF(I2<24,B2,IF(I2>23,I2*C2,IF(I2>100,I2*E2,IF(I2> 300,I2*F2,IF(I2>500,I2*G2,IF(I2>1000,I2*H2,""))))) )
The IFs are solved left to right. If I2=102,
It is not less than 24, so it goes to next one
It >23 so it results in I2*C2. It never checks to see that if it is >100 or >300 , the IF checks that 101>23 so it does the "True part"
Steve

20040902, 10:40 #11
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula / calculation problem (Excel 2000)
Thanks everyone it is working fine now
Steve

20040902, 14:45 #12
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula / calculation problem (Excel 2000)
Sorry to be a pain but it has just dawned on me that with 100's of different entries all possibly with different minimums and different 50 rates it will take me years to keep writing the formulas for each one, trying to be bright I tried
=IF(B2/I2<B2,B2,IF(B2/I2>B2,C2*I2,IF(I2>50,D2*I2 etc
What I am trying to get itto do is to divide the minimum by the 50 rate, if that is less than the weight entered in I2 then J2 should show the minimum (B2), if the minimum divided by the weight is greater then j2 should show the weight X the value of C"
hope that makes sense
cheers
Steve

20040902, 15:15 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: formula / calculation problem (Excel 2000)
I am not sure what you are after. Could you elaborate with some exmple data?
=IF(B2/I2<B2,B2,IF(B2/I2>B2,C2*I2,IF(I2>50,D2*I2 etc
You are not looking at the conditions again
If I2>50 then B2/I2<B2 is true, so this condition (D2*I2) will never be calc'd, it will give B2
Steve

20040902, 15:47 #14
 Join Date
 Jan 2001
 Location
 Ankeny, Iowa, USA
 Posts
 298
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula / calculation problem (Excel 2000)
Do you mean that you first want to test that the minimum (col [img]/forums/images/smilies/cool.gif[/img] divided by the 50 rate (col C) is at least the amount in the Weight column (col I)? Is that where the 24 comes from in your original question?
And are you saying that you don't want to have to determine the minimum divided by the 50 rate for every shipment so that you don't have to calculate that and write it into each formula in every row? Is that what you're concerned will take forever to do?
If so, just change the reference in the formula in row 2 from '24' to 'B2/C2'.
Otherwise, I don't think I understand what you're wanting to do.

20040903, 07:48 #15
 Join Date
 Sep 2003
 Location
 London, Gtr London, United Kingdom
 Posts
 153
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula / calculation problem (Excel 2000)
Hi Steve
Thanks for your ongoing help. Please see the attached example and the following code which I have tried to do without any success
=IF(B59<B59/I59,B59,IF(B59>B59<50,C59*I59,B59*C59,IF(I59>1000, H59*I59,IF(I59>500,G59*I59,IF(I59>300,F59*I59,IF(I 59>300,F59*I59,IF(I59>100,E59*E59,IF(I59>50,D59*I5 9,""))))))))
If you look at the attachment you will see that each destination (A) has a minimum ([img]/forums/images/smilies/cool.gif[/img] with varying 50 kilo rates © therefore if a customer had a shipment of 23 kilos going to a destination where the Minimum ([img]/forums/images/smilies/cool.gif[/img] is