+ Reply to Thread
Results 1 to 4 of 4

how to lookup value and insert into a mathematical formula.

  1. #1
    Registered User
    Join Date
    06-15-2016
    Location
    Grimsby ON
    MS-Off Ver
    2016
    Posts
    3

    how to lookup value and insert into a mathematical formula.

    hello all,

    I am trying to do a payroll spread sheet for transportation. The problem is that the pay scales are based on daily mileage, and thus rate of pay per mile, and delivery rate are affected.

    This is the array I created for the pay values. The two columns on the left are the mileage ranges, the $per km column is the value I want to use for the mileage portion of the pay, to be entered in one cell of the spread sheet, and the value from the delivery row is to be multiplied by the value entered in the cell for number of delivery's.

    from to $per km delivery
    1 300 $0.32 $28.60
    301 625 $0.29 $26.00
    626 875 $0.27 $24.50
    876 1100 $0.24 $23.00

    This is the spread sheet that I will use

    Day Of the week MON TUE WED THUR FRI SAT SUN
    Zone
    Kilometers
    Drops
    Deliveries
    Single Store
    Delay Time
    Hourly
    Shunting
    Stat Holiday
    Hours Worked
    Daily Gross Income

    For example, 345km and 6 deliveries will pay $256.05.

    Lets use Monday for the example, when I enter 345 in the cell under Monday, it will look up the pay rate and multiply, and same for the Delivery rate. How ever I only want the total days earnings to show in the cell marked daily gross income under Monday.

    All help greatly appreciated.

    William

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    7,866

    Re: how to lookup value and insert into a mathematical formula.

    In this case, I would simply use the VLOOKUP() function https://support.office.com/en-us/art...8-93a18ad188a1 It seems that all examples are for "lookup_type"=FALSE find exact match, where your case is a perfect use for lookup_type=TRUE approximate match.

    Something like =345*VLOOKUP(345,$A$2:$D$5,3,TRUE)+6*VLOOKUP(345,$A$2:$D$5,4,TRUE) should yield 256.05. Note the TRUE in the lookup_type argument and that I am assuming your lookup table is stored in A2:D5.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-15-2016
    Location
    Grimsby ON
    MS-Off Ver
    2016
    Posts
    3

    Re: how to lookup value and insert into a mathematical formula.

    Thanks for your input. I modified the formula a bit to fit my application, how ever it didn't work.

    This is the formula I tried;

    =[b3*VLOOKUP(B3,$K$1:$L$4,3,TRUE)]+[B4*10.35]+[B5*VLOOKUP(B3,$K$1:$L$4,4,TRUE)]+[B6*10]+[B7*22.02]+[B8*22.02]+[B9*21.53]

    The 345 is only an example value, and it has to reference a cell number, in this case B3 is the cell where "345" would be entered.
    If it was on Tuesday, the cells would be C3, etc., and so on for the rest of the week.

    Let me know what I should correct.

    I had this working before, how ever the hard drive I saved it on got damaged.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    7,866

    Re: how to lookup value and insert into a mathematical formula.

    My version of Excel does not accept the square brackets [] as parentheses in formulas. Other than that, I see nothing syntactically wrong with the formula you have. I might suggest that you use the Evaluate formula tool (https://support.office.com/en-us/art...6-a70aa409b8a7 ) and see which step in the function is returning an incorrect value.

    If we are to help debug this, we need to know what is in B3, B4, B5, B6, B7, B8, and B9 as well as the values being returned by the lookup functions. We will also need to know the correct answer and the incorrect result that Excel is giving you. (a sample spreadsheet would probably be the easiest way to pass this information along).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1