Hi All,
I am trying to import a BASE ESTIMATE table into EXCEL.
I have problems with most of the formulas, especially this one:
=VLOOKUP($E$2,$B$24:$P$604,HLOOKUP($E$3,$D$22:$L$604,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$604,1),$D$22:$L$23,2)
and this one
=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10
I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?
Thank you so much in advance!
Last edited by NBVC; 10-19-2009 at 03:26 PM. Reason: If I don't mark it Solved, will you?
Post your workbook so we can see the structure and an answer will be forthcoming.
Thank you! Here is the sheet.
Maybe, you can describe in words what exactly you want to achieve with that formula...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
CORRECT FREIGHT COST =ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10
NEXT RATE UP
=VLOOKUP($E$2,$B$24:$P$53,HLOOKUP($E$3,$D$22:$L$53,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$53,1),$D$22:$L$23,2)
The sheet is basically a freight lookup table.
You type in the pounds (lbs) select the zip code and it looks up the miles, tells you how much to charge for freight and, if the (lbs) are close to the next discount level, it rounds the weight up, so the freight gets cheaper.
i am trying to get:
RATE X WEIGHT
NEXT RATE UP
MINIMUM
CEI FREIGHT DISCOUNT
CORRECT FREIGHT COST
ROUND TRIP MILES
to work for all zip codes and weights.
Thank you for your help...
or maybe, keep your formula in E4 and change the one in E6 to:
formatted as General to see the decimals
Please Login or Register to view this content.
Thank you for the answer, but it still produces errors.
I wonder if there are restrictions about HLOOKUP in HLOOKUP in excel
It didn't produce an error for me.... see attached.
The only restriction is that you don't have more than 7 functions nested in the formula.. (XL2003 and prior)... As long as the outcome of each nested function is acceptable as an argument for the function it is nested in, then you should get a result....
It produces errors if you go up in weight. Type 5000+ and it will produce an REF error.
Where?
I type 5000 or 6000 in E3 and I get results in E6 where I have the formula.
Yes, thank you! It seems to work
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
The sheet pulls up wrong data.
For a weight like 10,000 lbs
ZIPCODE: 33310
Minimum Freight is $71.
For 46000 lbs,
freight is $6041
It looks like the 39999 to 40001 lbs range is messed up.
Truckload prices of 40000 lbs plus should be relatively cheaper than lets say 35000 lbs.
HEEEEELP
Don't see these numbers in the workbook last posted... which formula (what cell) are you referring to? And what should the results be (from where?)
The weight and ZIP code have to be entered.
The resulting price is in the RATE X WEIGHT field. Which is supposed to be the freight rate (X) the weight in lbs.
I am not following this... where is the $71 showing up? Where is 6041 showing up.. what do you mean by 39999 to 40001 lbs?Originally Posted by gqdallas
The sheet pulls up wrong data.
For a weight like 10,000 lbs
ZIPCODE: 33310
Minimum Freight is $71.
For 46000 lbs,
freight is $6041
It looks like the 39999 to 40001 lbs range is messed up.
Truckload prices of 40000 lbs plus should be relatively cheaper than lets say 35000 lbs.
HEEEEELP
Hello All,
I attached the new freight cost table (short version) and a table with quotes generated by our Base Estimate program.
The numbers should match in the freight cost calculator, but they don't. The Freight Price and the Next Rate Up price are always different in the excel sheet than what Base estimate comes up with.
The only formula that had been changed in the past is the "next rate up" formula. Someone added a MATCH function. The results are pretty close, but there has to be a way to get the exact results Base Estimate comes up with.
Help is greatly appreciated! Thank you in advance...
Please use an example or 2 to illustrate what you mean.
Ok, I think this may be what you are after..
I am assuming you want the Next Rate Up in both sheets to match.
Try, in E6:
Please Login or Register to view this content.
Last edited by NBVC; 10-16-2009 at 04:01 PM. Reason: added attachment
This is awesome! It works 100% now.
I owe you big time
That's great...
Can you please remember to mark the thread as Solved...
see FAQ link in my signature for instructions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks