Could a pivot table solve this (extensive) problem?

I have a question to which I think I know the answer, but because it's so much work I'd like to confirm beforehand.

My problem is as follows: For driving to work, an employee gets several types of reimbursements. His trajectory is made up of 2 parts: part one from his home to a meeting point, part 2 from the meeting point to the work place. He gets different kinds of reimbursements for part 1 than he does for part 2. The distances of parts 1 and 2, separately, are both between 1 and 200 kilometers (rounded to natural numbers). Hence, there are a total of 40 000 (200*200) possible reimbursement amounts (assuming reimbursements vary per kilometer).

I'd like to show these 40 000 amounts in a matrix (so you could see quickly, for example: what the employee's reimbursement would be if his first trajectory is 25 km and his second 113).

I'm thinking of getting there as follows:

Make 40 000 Excel lines, column 1 containing the distance of trajectory 1 (a 200-times recurring loop of 1 to 200), column 2 containing the distance of trajectory 2 (200 times 1 for the first loop, 200 times 2 for the second, etc) and column C the reimbursement amount. Then I make a pivot with column 1 as a row heading, column 2 as a column heading and column C as a value.

Would this work?

Thanks

Hello Swarles & welcome to the forum.
In my opinion Pivot Table may not be the right choice Here. As per my understanding i think Vlookup or Index match may come more handy.
Lets wait form some experts opion. How about uploading a sample data file
Best Regards/VKS

Don't you just want the rate for part 1 times the distance for part 1 plus the rate for part 2 times the distance for part 2 ??

Pete

I will clarify: calculating any reimbursement based on any 2 distances is very easy; I could enter a couple of formula and create a sheet where you just enter the distance for trajectory 1 and the distance for trajectory 2 and you get the reimbursement amount.

The issue is that that's not really what I need. Imagine if you'd have to print every possible solution, with Excel as the source data, and you'd want to waste the least possible amount of paper.

Well, having 40,000 rows is not going to save you much paper (say 100 rows deep, than will be almost a full ream).

Better to do it as a table, with 0 to 200 going across on row 1 (starting from B1), and 0 to 200 going down in column A (starting from A2), and then you can have this formula in B2:

=B$1*rate_1 + $A2*rate_2

then copy this across and down to create your table. Then when you print that you can probably use only 3 pages going down and maybe 10 pages going across.

Hope this helps.

Pete

Ha a simple data table is actually what I wanted to get to. I just thought to get there through a pivot table, which is a huge amount of unnecessary work. I got it now, thanks guys.