# Thread: formula / calculation problem (Excel 2000)

1. ## 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

2. ## 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,"")))
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!

3. ## 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

4. ## 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.

Cheers

5. ## 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

6. ## 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 K2

7. ## 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.

Steve

8. ## 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

9. ## 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,""))))) )

10. ## 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)))))

=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

11. ## Re: formula / calculation problem (Excel 2000)

Thanks everyone it is working fine now

Steve

12. ## 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

13. ## 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

14. ## 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.

15. ## 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

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•