formula / calculation problem (Excel 2000)

Topic

New Reply

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 ( 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(J22396<285,K2=J2*D2,"") I do hope this makes some sort of sense to somebody.

TIA

Steve

Reply | Quote

Replies

Studying up your formula, I assume you are entering it into K2.
Try this editted version: =IF(J223,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!

Reply | Quote

Studying up your formula, I assume you are entering it into K2.
Try this editted version: =IF(J223,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!

Reply | Quote

You would put this in cell K2:
=IF(J223, j296,j2=285 it will yield a null string (“”), so you might want to adjust if that is not correct. (perhaps you want =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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 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,I615,I6500,I61000,I6<100000,I6*H6,""))))))

But it does not seem to want to know

TIA

Steve

Reply | Quote

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(I21000,I2*H2,IF(I2>500,I2*G2,IF(I2>300,I2*F2,IF(I2>100,I2*E2,IF(I2>23,I2*C2,””))))))

Reply | Quote

Thanks everyone it is working fine now

Steve

Reply | Quote

Thanks everyone it is working fine now

Steve

Reply | Quote

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/I2B2,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

Reply | Quote

I am not sure what you are after. Could you elaborate with some exmple data?

=IF(B2/I2B2,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

Reply | Quote

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(B59B591000,H59*I59,IF(I59>500,G59*I59,IF(I59>300,F59*I59,IF(I59>300,F59*I59,IF(I59>100,E59*E59,IF(I59>50,D59*I59,””))))))))

If you look at the attachment you will see that each destination (A) has a minimum ( with varying -50 kilo rates © therefore if a customer had a shipment of 23 kilos going to a destination where the Minimum ( is

Reply | Quote

If you convert C3:H3 to numbers (they are currently text: you can format – cells custom “+”0;”-“0 to get the “+” to display)
Then you can use the formula:
=MAX(B4,INDEX(C4:H4,MATCH(I4,$C$3:$H$3))*I4)

To do what you want without the IFs. It lookups the weight in the list from C3:H3 and takes the appropriate value in the current row in that column and multiplies it by the weight. It takes the max of that number and the minimum for the weight.

This formula does not have to be modified for any changes, you just have to change the “header” or the values. It can be copied down the rows. The “header” for lookup is locked, the other cells are relative.

Steve

Reply | Quote

Thank you so much, thats just the ticket

You did almost get a reply saying it did not work and as a typed away I had one of those ‘slap your forehead’ moments, because I printed your last response from another machine I manually typed the formula into my laptop, I could not understand why you had 14 (fourteen) in the formula but I thought what the heck your the expert. As I was typing though it dawned on me it was I4 (a case for lower carriage in codes?).

Thanks again and have a great weekend

Steve

Reply | Quote

Thank you so much, thats just the ticket

You did almost get a reply saying it did not work and as a typed away I had one of those ‘slap your forehead’ moments, because I printed your last response from another machine I manually typed the formula into my laptop, I could not understand why you had 14 (fourteen) in the formula but I thought what the heck your the expert. As I was typing though it dawned on me it was I4 (a case for lower carriage in codes?).

Thanks again and have a great weekend

Steve

Reply | Quote

Hi Steve

I am really sorry to be a pain but can you help me again, I have attached an example where, in book 1(airport to airport) I applied the formula that you helped me with, in book 2 (airport to door) I needed to add another column but now I keep getting the #N/A error, I have checked that each cell in book 2 has the same behaviour (i.e. each cell is formatted the same to currency, number etc.) and changed your formula slightly to reflect the extra column.

Your advice = =MAX(B4,INDEX(C4:H4,MATCH(I4,$C$3:$H$3))*I4) (C3 – H3 being numbers)

My attempt with the extra column = =MAX(B11,INDEX(C11:I11,MATCH(J11,$C$10:$I$10))*J11) (making sure that C3 – I3 are numbers)

Any advice and explanation greatly appreciated

Cheers

Steve

Reply | Quote

c10 – i10 are entered as text. what’s in row 3 doesn’t matter for this formula as they aren’t referenced in the formula.

Reply | Quote

Thanks Ban

I did check that they were number columns, did they change to text because I used the ‘ to add the minus and plus signs?

Cheers

Stephen

Reply | Quote

If you add ‘ in front of any cell contents, that will automatically change them into text – no matter how they are formatted. HTH

Reply | Quote

If you add ‘ in front of any cell contents, that will automatically change them into text – no matter how they are formatted. HTH

Reply | Quote

Yes, get rid of the “‘” or “^” and just put in the number. Change the number format as I mentioned earlier. This will keep them numbers but display the “+” or “-”

Steve

Reply | Quote

Yes, get rid of the “‘” or “^” and just put in the number. Change the number format as I mentioned earlier. This will keep them numbers but display the “+” or “-”

Steve

Reply | Quote

Thanks Ban

I did check that they were number columns, did they change to text because I used the ‘ to add the minus and plus signs?

Cheers

Stephen

Reply | Quote

c10 – i10 are entered as text. what’s in row 3 doesn’t matter for this formula as they aren’t referenced in the formula.

Reply | Quote

If you convert C3:H3 to numbers (they are currently text: you can format – cells custom “+”0;”-“0 to get the “+” to display)
Then you can use the formula:
=MAX(B4,INDEX(C4:H4,MATCH(I4,$C$3:$H$3))*I4)

To do what you want without the IFs. It lookups the weight in the list from C3:H3 and takes the appropriate value in the current row in that column and multiplies it by the weight. It takes the max of that number and the minimum for the weight.

This formula does not have to be modified for any changes, you just have to change the “header” or the values. It can be copied down the rows. The “header” for lookup is locked, the other cells are relative.

Steve

Reply | Quote

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(B59B591000,H59*I59,IF(I59>500,G59*I59,IF(I59>300,F59*I59,IF(I59>300,F59*I59,IF(I59>100,E59*E59,IF(I59>50,D59*I59,””))))))))

If you look at the attachment you will see that each destination (A) has a minimum ( with varying -50 kilo rates © therefore if a customer had a shipment of 23 kilos going to a destination where the Minimum ( is

Reply | Quote

I am not sure what you are after. Could you elaborate with some exmple data?

=IF(B2/I2B2,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

Reply | Quote

Do you mean that you first want to test that the minimum (col 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.

Reply | Quote

Do you mean that you first want to test that the minimum (col 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.

Reply | Quote

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/I2B2,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

Reply | Quote

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(I21000,I2*H2,IF(I2>500,I2*G2,IF(I2>300,I2*F2,IF(I2>100,I2*E2,IF(I2>23,I2*C2,””))))))

Reply | Quote

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(I223,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

Reply | Quote

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(I223,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

Reply | Quote

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 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,I615,I6500,I61000,I6<100000,I6*H6,""))))))

But it does not seem to want to know

TIA

Steve

Reply | Quote

You would put this in cell K2:
=IF(J223, j296,j2=285 it will yield a null string (“”), so you might want to adjust if that is not correct. (perhaps you want =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

Reply | Quote

Hi Steve,

Check out the thread starting at post 403669. It discusses various ways to address a related problem.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Steve,

Check out the thread starting at post 403669. It discusses various ways to address a related problem.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Here is a reworked example if you need to evaluate between 2 specific ranges:
=IF(J223,J296,J2<285),J2*D2,"")))
Enter it into cell K2

Reply | Quote

Here is a reworked example if you need to evaluate between 2 specific ranges:
=IF(J223,J296,J2<285),J2*D2,"")))
Enter it into cell K2

Reply | Quote