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…?
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?
ii. If
Volumes is > 5 or < 1 what should happen? (below the first range or 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?
ii. What
about 5.1 (if your data might contain fractions)? Would that be the first
range or the second?
iii. What
about < 1 or > 100? (below the first range or above the last range)
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
<mailto:excel-macros%2bunsubscr...@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
