Questions

excel argument help

Tags:
+
0 Votes
Locked

excel argument help

gulfstream40
I am having trouble developing a formula for the following:
in one cell ranges by weight from 1 to 500
501 to 1000
1001 to 2000
2000 >
I need to * this by a rate applicable for each weight and creat the sum for shipping cost.The formula need to select the * based on the weight within the ranges.

Having trouble developing a formula
  • +
    0 Votes
    Eddie, GH

    Use 'Conditional IF' function to set this rule.

    Here's a sample statement that you would type into the cell where the result of the multiplication would be (in this case column B):
    =IF(A3<501,A3*1,IF(A3<1001,A3*2))

    Results:
    255 255
    300 300
    587 1174
    60 60
    1005 FALSE

    Now the explanation:
    the 'A' column is where you have the figures for your range; e.g. 255, 300, etc.

    In the example above, '<501' is a condition for the operator to multiply the figure in the column A by 1. ie. 'A3*1'
    Also, '<1001' sets the condition to multiply by 2.

    You can change the factors 1, 2, etc.

    Take note of the bracket symbols. These are called nested functions. When the condition after the first bracket opener is satisfied, the calculation stops. Otherwise the operation moves into the next bracket, and so on.

    You notice the 'false' result in the last row. This is because no factor has been set to multiply where A3 is more than 1000. So make sure that your function statement covers the maximum possible figure you will encounter.

    Also, you can open more brackets (up to a max 20 - if I remember correctly). Remember, the number of bracket openers must correspond to the number of bracket closures, and all the bracket closures occur at the end of the statement.

    Good luck.

  • +
    0 Votes
    Eddie, GH

    Use 'Conditional IF' function to set this rule.

    Here's a sample statement that you would type into the cell where the result of the multiplication would be (in this case column B):
    =IF(A3<501,A3*1,IF(A3<1001,A3*2))

    Results:
    255 255
    300 300
    587 1174
    60 60
    1005 FALSE

    Now the explanation:
    the 'A' column is where you have the figures for your range; e.g. 255, 300, etc.

    In the example above, '<501' is a condition for the operator to multiply the figure in the column A by 1. ie. 'A3*1'
    Also, '<1001' sets the condition to multiply by 2.

    You can change the factors 1, 2, etc.

    Take note of the bracket symbols. These are called nested functions. When the condition after the first bracket opener is satisfied, the calculation stops. Otherwise the operation moves into the next bracket, and so on.

    You notice the 'false' result in the last row. This is because no factor has been set to multiply where A3 is more than 1000. So make sure that your function statement covers the maximum possible figure you will encounter.

    Also, you can open more brackets (up to a max 20 - if I remember correctly). Remember, the number of bracket openers must correspond to the number of bracket closures, and all the bracket closures occur at the end of the statement.

    Good luck.