HI, the table in the example transporter AA the volumes are the columns "1", "2 to 3", and "4 to 5", the column below is the weight, but there are some rules, imagine we have zone 1, but the kg are 8 but the volumes are 1, i´m must charge 2 instead of one, because the rule weight overules the other, whenever one rule is "bigger" than the other, we charge considering the highest value.
i´m trying to do this with a table concatenating all the values and then using lookups, let´s see if I can succeed. 2012/6/29 Asa Rossoff <a...@lovetour.info> > Hi Jorge,**** > > Looks like this can be handled using lookup formulas.**** > > ** ** > > One good way to perform lookups is to have a number representing each of > your criteria each alone in their own cell, preferably all in the same > table for all transporters. You can use pivot tables to generate the cross > referenced charts you have now for easy human-reading for each shipper, and > your Cost table can use lookup formulas to do its calculation.**** > > ** ** > > I'm a bit confused about a few details, and don't have a lot of time right > now, but will try to take another look in the coming days if you haven't a > solution yet.**** > > ** ** > > Some questions though:**** > > You refer to 6 items of data: (1) Transporter (2) Zone (3) Volumes (4) > Weight/KG (5) Distance/KM (6) Cost.**** > > ** ** > > The charts for each Transporter seem to contain 4 items of data. 5 > including the name of the transporter. I assume the row 2 of the > transporter tables contain ranges for VOLUMES, row 3 ranges for WEIGHT/KG; > Column 1 zontains ZONE. The data points in cells B4:D7 then must contain > either DISTANCE/KM or COST… But which? And where is the other item? > > **** > > ** ** > > It's possible to perform a lookup that checks multiple criteria and finds > the intersection on a table like this. Perhaps--since the ranges for > volumes and KG seem to be the same for all transporters, as do the number > of zones--insert another column either before or after the zone column for > the Shipper name/ID, and include all transporters' data in the one table > while maintaining a similar format. You can then use a lookup strategy > that might go like this: use MATCH functions for each criteria, and logic > to determine the right combination of criteria, then use the index function > to retrieve the selected column, and another index function to retrieve the > selected row. Place a space between those two functions (all in the same > formula) and Excel will return the intersection of that row and column, > which will be a single matching cell.**** > > ** ** > > Asa**** > > ** ** > > ** ** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *Jorge Marques > *Sent:* Friday, June 29, 2012 1:54 AM > *To:* excel-macros@googlegroups.com > *Subject:* $$Excel-Macros$$ What do you recommend?? If Formulas, Tables > with macros, vlookups?**** > > ** ** > > Hi guys i have a sheet where I every month i calculate the costs for > logistics, there are 3 conditions (KM, Weight in KG, and Volumes), where i > have to calculate depending on the data put in the columns B,C,D**** > > ** ** > > if it is zone 1,2,3 and has the volumes or weight under those limits it > puts those values. if zone 4 and under those 3 limits we have to multiply > the value in the table with the extra km in column D.**** > > ** ** > > Until now i only had 2 transport company´s so I used if formulas to > calculate the costs, but now I have 3 more, a total of 5, and I think if i > use if formulas it will be heavy on the code and generate errors.**** > > ** ** > > What do you recommend?**** > > ** ** > > Thank you very much in advance.**** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@googlegroups.com**** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@googlegroups.com > -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
