I am trying to figure out a better way to do my mileage for when I drive for work.
Currently I need to look at a sheet and see where I started and where I stopped and then I’ll see the distance.
Kinda look something like this.
Home
Work
School
Home
031Work
304School
140
What I would like to do is type in the “to” and “from” cell and have it automatically know the miles based on the chart above.
DateToFromDirectionTimeMiles3/4/10Home
Work
This is for mileage purposes. I have two sheets. 1st sheet I have Date, From, To, Mileage. I want the Mileage cells to automatically equal the mileage it is from and to. Right now I have it setup like this on the 1st sheet B7 is Date, C7 is From and D7 is To and E7 is Mileage. I have a drop down menu for C7 and D7 for our facilities such as Ming Ave (MNG), Coffee Rd(COF) and so on. On the 2nd Sheet I have the chart that show the miles.
example MNG to COF is 8.3 miles.
I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 Replies View RelatedUsing VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
trying to figure out a formula that would save me heaps of time trying to calculate mileage on expense reports each month, and I cannot for the life of me get the formula down. Following are the specifics:
Mileage is reimbursed at a rate of $0.17/mi for the first 198 miles in the week. Any mileage after 198 is calculated at a rate of $0.56/mi. The mileage log I have is a monthly log, and expenses are turned in on a monthly basis.
Is there a way to create a formula that will calculate on a weekly basis, at the correct rate based on the number of miles in that week thus far?
I am trying to create a formula which will display tarriffs for a taxi depending on how far it has travelled -
Distance Tarriff
0-0.5 miles$1.00
0.6-2 miles$0.80
3-5 miles $0.75
6-20 miles$0.70
I want to enter the exact mileage travelled into a cell e.g. 8 miles, then I would like the exact cost to be shown in the cell below. In this case the cell will display $0.70 for 8 miles.
I'm working on creating a form where truck drivers can calculate their mileage to and from job sites. What I have now is two lists, one is a "to" list, where it lists the Job Sites, and one is a "from" list, where it lists the destinations (usually to Rock Quarries). These two lists are being referenced in the user form as combo boxes. An additional list holds the distances for each to and from possibility.
What I would like to do is when the user selects a to and from location from the combo boxes in the form, the form references the mileage distance from the list of to-from possibilities.
I have created a gas mileage chart where the MPG line will change from one color to another as the "type" of driving changes (Highway to City). However, the chart also ignores any non-full tank data to eliminate spikes that arise in your MPG calculations when you don't fill your tank up all the way.
I have attached a spreadsheet detailing what i have done and would like to know if anyone can think of an easier way to replicate this chart.
I am trying to create a very simple mileage log that would get data from one sheet when I input it into another. (I attached the sheet). I have it set up so that it the formula is on bottom of sheet labeled store to store mileage but I would like to be able to track it easier on a different sheet called mileage log. So basically what I would like to do it put a formula in the mileage log so that I can just type in the store number in the to and from cells and have it populate it from the store to store mileage shet. I am an amateur. If it is easier I can just make another simple excel sheet that is on a seperate worksheet instead of using the mileage log.
View 4 Replies View RelatedI'm creating a mileage log for my work as a traveling art teacher. Most templates have you just input the number on the odometer when you started and when you finished. Since I can never remember to check the odometer when I am getting in and out of the car I thought it might be easier to just record the distance from each location once and then record that number of miles each trip. I usually travel the same route so the mileage is always the same.
I have an array of numbers right now, with 3 columns listing my 3 most common start locations. Then I have 5 rows listing the 5 schools I travel to for work, and the distances from each start to each end in the array. On a separate sheet I have my log.
What I would like to be able to do is select a start location and an end location from a drop down list on the log and have the number of miles automatically appear in the miles column. Then I can double this for the return trip and total it at the bottom and calculate my expense.
I am having trouble writing a formula that works for all 15 possible distances, and I don't quite know how to write it so that it references my array and finds the correct distance for those two specific choices. So far I have the drop down menu's pulling from the array but I'm stuck at how to index the array using the information that is coming from the drop down list.
Since I travel to these locations multiple times I think it would be a lot faster than looking up the distance every time and typing it up manually.
I am trying to create a multiple v look up formula to calculate the mileage from two destinations. I have a table with a From column, To Column, and miles column with data.
I would like to automatically calculate the mileage determined upon what the user picks as the two destinations from a drop down list. I thought it would work like this =VLOOKUP(B5:C5,G3:I14,2) But I'm not sure if I can have a range as the lookup value. I have attached my sheet I am working from.
I have a list with all our company cars (300). I also have a list with all the transactions of the fuel cards (12000 lines). Now I need the find for every company car (exact match) the nearest fuel-consumption-date (closest match to the given starting date) and take the mileage of that transaction. I've added an example. I have tried linking carnr and date (like carnr&"#"&date) and match these, but I don't always get the right car number.
View 1 Replies View RelatedI'm trying to do is calculate pay for mileage while factoring in the number of participants. In other words, if the mileage was between 0 and 50 miles, and there were 5 or less people, then this cell should be $35.00. But if it's between 0 and 50 and there were more than 5, then this cell should display 175/the number of people (175 being 35 * 5). In other words, more than five people would receive an equal amount of the total pay for five people (this is because five people are the standard, but there are instances in which six or seven could participate and I was hoping to avoid hardcoding that).
The rest of the if statements simply follow that same formula, but for different mileage/pay rates.
I have a list of mileage rates that correspond with different date ranges. For example the mileage rate for March 19, 2008 – July 31, 2008 was 50.5 cents per mile. I have 11 different mileage rates / date ranges from 2000 onwards.
I would like to devise a way that I could input the date and the number of miles travelled into the spreadsheet, then Excel would check the date entered in order to choose the appropriate mileage rate and calculate the dollar amount based on the # of miles input.
Here are the date ranges and rates in the event that anyone is interested in putting something like this together. I would be grateful for an example for one or two of the rates though and hopefully could extrapolate from there.
1/1/00 - 3/31/00 - 32.5 cents per mile
4/1/01 - 12/31/01 - 34.5
1/1/02 - 12/31/02 - 36.5
1/1/03 - 12/31/03 - 36
1/1/04 - 12/31/04 - 37.5
1/1/05 - 8/31/05 - 40.5
9/1/05 - 12/31/05 - 48.5
1/1/06 - 1/31/07 - 44.5
2/1/07 - 3/18/08 - 48.5
3/19/08 - 7/31/08 - 50.5
8/1/08 - current - 58.5
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
The spreadsheet i am working on requires mileage between last trip and previous trip.
Unfortunately there will be blank cells between as shown below ...
Below there are all the information needed to understand my problem.
M(1 to R,1 to C): matrix with R rows and C columns [element known]
M(i,j): elements of the matrix M in position i(row)-j(column) [element known]n
I need to get the fee value, if I write "Name" and "Time".
Please read the simple example file named "Fee from matrix".
Given one array variable (matrix n x n) M, I want to use the function sum, to sum along the rows.
I can use Application.WorksheeFunction.Sum(M)
but this will sum all the elements
and I would like smtg like this
Application.WorksheeFunction.Sum(M(1,)
Just sum the first line, or the i-th line.
I have code to create a correlation matrix (NxN, where N is the number of columns). This is done by selecting an area that is NxN, entering the function and range, then hitting ctrl +shift + enter (array formula).
However, I want to convert this to accept VBA arrays, rather than a data range, and give the output in form of an array as well.
VB:
Function CorrmatK(dataRange As Object) As Variant
On Error Goto 20
Dim r As Integer, n As Integer, rr As Integer, i As Integer, j As Integer, k As Integer, doit As Integer
Dim x() As Variant, mc() As Double, ss() As Double, m() As Double, ob As Object
r = dataRange.Rows.Count
n = dataRange.Columns.Count
[Code] ......
I NEED some code to convert a matrix to a singel column and it should be reversed to!
see attachment!
example:
FROM:
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
TO:
D1
C1
B1
A1
D2
C2
B2
A2
I have to create a matrix from columns in this format:
A1 A2 1
A1 A3 4
A1 A4 6
A1 A8 4
A2 A5 2
A2 A8 3
with a lot of empty cells (here, for exemple A1,A6 & A1,A7 ) and (A1,A2) = (A2,A1)
I'd like to have :
_ A1 A2 A3 A4 A5 A6 A7 A8 .....
A1 0 1 4 6 0 0 0 4
A2 1 0 0 0 2 0 0 3
etc
The source data is in the attachment. Would it be possible to do it automatically?
I have this code to take an area of data and perform the SUMXMY2 formula and output the results to another sheet. My problem is that the results are being outputted in a semi-matrix and i needed them in a column so i can them perform a sort. Is this possible and can anyone shed a light on the best way to do it?
View 2 Replies View RelatedI'm trying to do the inverse matrix of a matrix captured by an inputbox, but it doesn't work
This is the code:
Code:
option explicit
option base 1
sub matrix()
Dim M() As Double
Dim B() As Double
Dim Y() As Double
Dim columnas As Range
[code]......
the error is: Error 13 the types do not match
I have a matrix A with 12 rows and 10 columns. My problem is if in the cell(i,j) there is data then the same data should appear in a similar matrix B, but in a cell which is 15 cells behind the cell(i,j).
That is it should start counting upwards from cell (i,j) in B and once it reaches the top of the matrix it should continue counting from the bottom of the immediate left column and go up. When it reaches the 15 cell from cell(i,j) in b, it should print there the value that was in cell(i,j) of A.
I wasn't able to attach the file because it was too big, but you can download it from here www.easygcc.com/correl.rar. On the sheet called " Correlation" there is matrix, I am baiscally trying to fill in the correlation formula into every cell so the matrix is filled out. The data for the correlation calculation comes from the sheet called "Tech Data". I have filled in the correlation formula into a few of the cells as an example, but I don't want to continue doing this manually but rather have a macro do it for me. Otherwise this will take for ever. My macro is also a part of the file, if you would liek to look at it and maybe fix what I already have.
View 4 Replies View RelatedI have a 5 x 5 matrix. When values are entered in 2 cell there needs to be a matrix lookup and the corresponding value needs to be entered into the 3 cell. Example :
- A B C D E
V 1 2 2 3 3
W 2 2 3 3 3
X 2 3 3 4 3
Y 3 3 4 4 5
Z 3 3 4 5 5
So if X and D are entered into the 2 cells the 3rd cell should show 4.
I would like to do the following transformation in excel:
imagine the following matrix:
1 2 3
4 5 6
I would like to transform this into:
1
4
2
5
3
6
This is a simple version of my matrix, in reality it is much bigger, and I do not want to copy paste each column by hand. Any ideas?
I'm trying to create a risk matrix like the attached example. I want to be able to enter a value and to have it to appear in the right quadrant.
View 2 Replies View RelatedI'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4
Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.
I've got a list of zones in my From column and also in my To column.
I need to write a formula that will give my customers the Zones Charged in a seperate cell.
I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.
FREE Deliveries to anyone in Long Beach, CA to the winner!!!