I need help creating a formula

[Guest]

Hi, I really hope someone out there can help me, I am trying to create a
formual
that will allow me to calculate freight charges. I want to be able to input a
weight and a zone and have it come back with the cost. This is basically
what the sheet looks like now,
Sheet 1 Cell C27 (Weight) 5
Sheet 1 Cell C29 (Zone) 51
Sheet 1 Cell C31-Formula
Now based on my data on sheet two using 5lbs and zone 51 should
make the answer $13.31. The weights from 1 to 50 are in Column A2:A51
on sheet 2 and the Zones are B1:1 and the charges are in Columns
B2:B51, C2:C51,D251,E2:E51. None of the formulas I have tried work.
Can anyone out there help me with giving me the exact formula
I should use based on the information I gave to make this work

I appreciate any and all help you can give me.

Thanks alot

 

[Ron Rosenfeld]

Hi, I really hope someone out there can help me, I am trying to create a
formual
that will allow me to calculate freight charges. I want to be able to input a
weight and a zone and have it come back with the cost. This is basically
what the sheet looks like now,
Sheet 1 Cell C27 (Weight) 5
Sheet 1 Cell C29 (Zone) 51
Sheet 1 Cell C31-Formula
Now based on my data on sheet two using 5lbs and zone 51 should
make the answer $13.31. The weights from 1 to 50 are in Column A2:A51
on sheet 2 and the Zones are B1:1 and the charges are in Columns
B2:B51, C2:C51,D251,E2:E51. None of the formulas I have tried work.
Can anyone out there help me with giving me the exact formula
I should use based on the information I gave to make this work

I appreciate any and all help you can give me.

Thanks alot

I assumed that you would want the weight rounded up to the next pound, since
that's how I usually pay freight.

Given your data, and assuming that the Zones are in B1:E1 rather than as you
wrote (B1:1), I think this formula should work -- but check them with your
data:

=VLOOKUP(CEILING(C27,1),Sheet2!A1:E51,MATCH(Sheet1!C29,Sheet2!A1:E1))


--ron

 

[Guest]

Hi,

Try the following formula,

=INDEX(Sheet2!$A$1:$E$51,MATCH($C$27,Sheet2!$A$1:$A$51,0),MATCH($C$29,Sheet2!$A$1:$E$1,0))

Note that the weight you enter in C27 (in Sheet 1) has an exact match in one
of the cells in A2:A51 of Sheet 2 (for example, you can not enter 50.8 lb in
C27, since Sheet 2 Column A will not have such an entry; so you should enter
it as 51 lb). To avoid this problem use ROUND($C$27,0) or ROUNDUP($C$27,0)
instead of $C$27 in the formula, depending on how you round off partial
weights, e.g., 51.2 lb as 51lb or 52 lb)

Regards,
B. R. Ramachandran

 

[Guest]

Hi, I tried the formula and it didnt wok, it came back with an error #n/a
excel is saying the error is in the Col_Index_Num, I have no idea what that
means.

 

[Guest]

Hi, sorry but the formula didnt work, it came back with a #n/a error, excel
shows the error as COL_INDEX_NUM. Sorry.

 

[Ron Rosenfeld]

Hi, I tried the formula and it didnt wok, it came back with an error #n/a
excel is saying the error is in the Col_Index_Num, I have no idea what that
means.

Col_Index_Num? Where did that come from? It's not inherent in Excel.

I did not use that NAME in my formula, and you did not mention it in your
initial post in this thread.

I suspect you did not use the formula I provided, and/or did not set up your
data table in the way you described it in your initial post.


--ron

 

[Guest]

Ron I copied your formula exactly and obviously you know a great deal
about formulas this is exactly the way my spreadsheet looks.
Sheet 1- Completely blank except for Cell 27 (5), Cell 29 (51)
and Cell 31 (Formula).
Sheet 2 looks like this:
Column A B C D E
Weight 51 52 53 54
Row2 1 $10.36 $10.53 $11.00 $27.82
3 2 $11.14 $11.53 $12.17 $28.72
4 3 $11.85 $12.67 $13.53 $33.29
5 4 $12.52 $13.25 $14.60 $37.69
6 5 $13.11 $14.06 $15.56 $40.38

This is all the data I have put in so far but eventually I would
put lots more once I have the formula working. Based on this
the formula I enter on sheet 1 in Cell 31 should come back
with a cost of $13.11 but when I put your formula in cell
31 I got an error, the error is returned said #N/A.

 

[Ron Rosenfeld]

Ron I copied your formula exactly and obviously you know a great deal
about formulas this is exactly the way my spreadsheet looks.
Sheet 1- Completely blank except for Cell 27 (5), Cell 29 (51)
and Cell 31 (Formula).
Sheet 2 looks like this:
Column A B C D E
Weight 51 52 53 54
Row2 1 $10.36 $10.53 $11.00 $27.82
3 2 $11.14 $11.53 $12.17 $28.72
4 3 $11.85 $12.67 $13.53 $33.29
5 4 $12.52 $13.25 $14.60 $37.69
6 5 $13.11 $14.06 $15.56 $40.38

This is all the data I have put in so far but eventually I would
put lots more once I have the formula working. Based on this
the formula I enter on sheet 1 in Cell 31 should come back
with a cost of $13.11 but when I put your formula in cell
31 I got an error, the error is returned said #N/A.

I still don't see where you got "Col_Index_Num" in an error message. Where,
exactly, did that come from? Where did you see it?

How is the entry made in C29? Do you enter it directly or is it the result of
some formula.

If the latter, post the formula.

If the former, is it possible that the entry in C29 (Zone) is text? Check this
by executing the formula: =ISTEXT(C29).

If it is TEXT, change the format to General and then re-enter the zone number.


--ron

 

[Ron Rosenfeld]

Ron I copied your formula exactly and obviously you know a great deal
about formulas this is exactly the way my spreadsheet looks.
Sheet 1- Completely blank except for Cell 27 (5), Cell 29 (51)
and Cell 31 (Formula).
Sheet 2 looks like this:
Column A B C D E
Weight 51 52 53 54
Row2 1 $10.36 $10.53 $11.00 $27.82
3 2 $11.14 $11.53 $12.17 $28.72
4 3 $11.85 $12.67 $13.53 $33.29
5 4 $12.52 $13.25 $14.60 $37.69
6 5 $13.11 $14.06 $15.56 $40.38

This is all the data I have put in so far but eventually I would
put lots more once I have the formula working. Based on this
the formula I enter on sheet 1 in Cell 31 should come back
with a cost of $13.11 but when I put your formula in cell
31 I got an error, the error is returned said #N/A.

Oh, also do =ISTEXT(Sheet2!B1)




--ron

 

[Guest]

Hi again, okay C29 is general, there is no formula and I checked the cell.
I got the error by doing an "insert function" and a box came up with the
following:

Function Arguments
lookup value= c27,1=5
table array=sheet 2a1:a51=ref,ref,ref
col.ind.num=match,sheet1!c29,sheet21a1:e1=#n/a

Kim

 

[Guest]

Ron C29 sheet 1 is general and sheet 2 B1 is general.

Kim

 

[Ron Rosenfeld]

Hi again, okay C29 is general, there is no formula and I checked the cell.
I got the error by doing an "insert function" and a box came up with the
following:

Function Arguments
lookup value= c27,1=5
table array=sheet 2a1:a51=ref,ref,ref
col.ind.num=match,sheet1!c29,sheet21a1:e1=#n/a

Kim

If you are getting those results, it looks as if you pasted in the wrong
formula into C31. The formulas are wrong. Nothing on any of those lines that
you pasted matches the formula I posted.

Try the following:

1. Select the formula below; then Edit/Copy

=VLOOKUP(CEILING(C27,1),Sheet2!A1:E51,MATCH(Sheet1!C29,Sheet2!A1:E1))

2. Select Sheet1!C31
Place cursor in formula bar at the top of the worksheet.
Edit/Paste
<Enter>

See what you get.

If, after doing the above, you hit Insert/Function, what you *should* be seeing
is:

Lookup_value CEILING(C27,1) =5
Table_array Sheet2!A1:E51 ={"Weight","51","52",
Col_Index_num MATCH(Sheet1!C29,Sheet2!A1:E1) =3

If you con't see that, change it so you do.



--ron