# how to lookup value and insert into a mathematical formula.

1. ## 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. ## 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.

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. ## 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).

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

#### 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