Hi Asa thank you very much for your help, let me see if i can explain better, I answered below the questions in yellow :)
2012/7/2 Asa Rossoff <a...@lovetour.info> > Hi Jorge,**** > > I am happy to help you.**** > > *> 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.* > > No problem. I think I understand all the values on the Transporter (e.g. > AA) tables now. You didn't explicitly state so, but I understand now that > the value being lookup up on this table (in B4:D7) is the Cost. I also > understand that the correct cost to select is the highest of the two costs, > for the given Volumes or Weight.**** > > ** ** > > ***1) ***Question, though; In your original message you said:** > > *>>> 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***** > > Here you stated that the Distance (KM) is a criteria for cost.**** > > **a. **Please tell me, on the Transporter AA table, where is the > Distance? Or in any case, please explain how you want the distance to be > considered. Perhaps the Cost is Cost per KM, and gets multipled as such in > the cost table…? > I only put the distance in column B if my "Order of purchase" is in zone 4, in zone 1,2,3 i leave that column blank, the zone column is the E > **** > > **2) **The ranges of values on your Transporter table are somewhat > unclear to me.**** > > **a. **Volumes: 1, 2-3, 4-5. If the Volumes number used for your > lookup is always a whole number with no fraction, this is clear, but..**** > > ** i. **If > it might contain a fraction, such as 1.3 or 1.9, what should happen? > Should it be rounded up to 2 for the lookup in both cases? Should 1.3 be > rounded down and 1.9 be rounded up? > The volumes are whole number because we designate pallets as volumes. > **** > > ** ii. **If > Volumes is > 5 or < 1 what should happen? (below the first range or above > the last range) > if volume is smaller than 1 then "error" if >5 goes to above the last range. > **** > > **b. **Weight: 1-5, 5-10, 10-100. Here your ranges actually overlap > at the edges -- this is different from how you defined your ranges for > Volumes.**** > > ** i. **Which > range should be used if the weight is exactly 5? The first range or the > second? > Here you should use the first range, my mistake sorry > **** > > ** ii. **What > about 5.1 (if your data might contain fractions)? Would that be the first > range or the second? should use first range until 5.99** > > ** iii. **What > about < 1 or > 100? (below the first range or above the last range)**** > > ** smaller than 1 "error", bigger than 100 should give "ask for budget", > if you wish i can put here my if formulas that i used for one transporter. > ** > > Asa**** > > ** ** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *Jorge Marques > *Sent:* Monday, July 02, 2012 2:34 AM > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ What do you recommend?? If Formulas, > Tables with macros, vlookups?**** > > ** ** > > 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**** > > -- > 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
