How to use Excel to determine maintenance Costs...Been playing with VLOOKUP but

[Pandy]

How to use Excel to determine maintenance Costs...Been playing with VLOOKUP but it doesn't seem to be working...Should I be using something else?


I work for a small company with 2 aircraft (soon to be 3). I would like to be able to input all my maintenance Invoices & the hours per month that that aircraft flies to determine what my hourly cost is.

I have 3 sheets.

The 1st Sheet is where I want to be able to input what aircraft & during what dates I want to look at @ have it give me the computed information.

The 2nd Sheet is where I list the maintenance invoices. Here each aircraft is listed when it was repaired, how much, ect.

The 3rd Sheet Has the Aircraft monthly hours. The Idea is to have these hours devided into the sum of the maintenance performed on that aircraft durring the aloted time period.

For example.

Durring 2008 the Aircraft flew 678 hours & The Maintenance spent on it was $43210. Thus 43219 divided by 678 = $63.73 per hour.


Any ideas?....So far all my attempts just end up with error messages.
Any suggestions would be greatly appreciated. I've included a copy of my current attempt.
http://simplycakes.info/PAA - Maintenance Costs.xls

 

[Punchin]

Looks like your main issue was your vlookup syntax. I adjusted the test formula in J5 to =VLOOKUP(J4,B3:H5,7,0), and made J4 (as a test) a dropdown list with the airplane names on it. Simply put this on the main sheet in the field you want the total cost to show in. Change J4 to the field with the airplane name in it. With vlookup, the array (B3:H5 in this case) can't include any cells to the left of the cells containing your reference column, and the "Range_Lookup" should either be a 0, 1, -1, or blank. If you look in the excel help (f1), it tells you what each number does. 0 means exact match and most of the time will be the one you want in there.

I think after you correct your formulas, the rest of the calculations will fall into place.


Correction: The excel help file only shows TRUE and FALSE for "Range_Lookup", but the options I provided give more control. 0 = false, 1 = true or blank, -1 = the opposite of 1 (go figure).

 

[sanrv1f]

try this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2						Time Period in Question
3						Aircraft	From	To
4						C-172 FIIU	01/01/2008	31/01/2008
5
6						Hours Flown
7						Number of Days	Air Time	Flight Time
8						30	55	60
9
10
11		Cost of Maintenance
12
13		For The Given Time Period				Per Air Time Hour				Per Flight Time Hour
14		Parts	Labor	Total		Parts	Labor	Total		Parts	Labor	Total
15		$ 225.00	$ 226.00	$ 255.00		$ 4.09	$ 4.11	$ 4.64		$ 3.75	$ 3.77	$ 4.25
16
Tracking Info

Excel 2003

Cell Formulas
Range	Formula
F8	=H4-G4
F15	=B15/$G$8
G8	=SUMPRODUCT(--(OFFSET('Aircraft Hours'!$A$1,3,MATCH(F4,'Aircraft Hours'!2:2,0)-1,10)>=G4),--(OFFSET('Aircraft Hours'!$A$1,3,MATCH(F4,'Aircraft Hours'!2:2,0)-1,10)<=H4),OFFSET('Aircraft Hours'!$A$1,3,MATCH(F4,'Aircraft Hours'!2:2,0),10))
G15	=C15/$G$8
H8	=SUMPRODUCT(--(OFFSET('Aircraft Hours'!$A$1,3,MATCH(F4,'Aircraft Hours'!2:2,0)-1,10)>=G4),--(OFFSET('Aircraft Hours'!$A$1,3,MATCH(F4,'Aircraft Hours'!2:2,0)-1,10)<=H4),OFFSET('Aircraft Hours'!$A$1,3,MATCH(F4,'Aircraft Hours'!2:2,0)+1,10))
H15	=D15/$G$8
B15	=SUMPRODUCT(--(Maintenance!$B$3:$B$105=$F$4),--(Maintenance!$C$3:$C$105>=$G$4),--(Maintenance!$C$3:$C$105<=$H$4),Maintenance!F$3:F$105)
C15	=SUMPRODUCT(--(Maintenance!$B$3:$B$105=$F$4),--(Maintenance!$C$3:$C$105>=$G$4),--(Maintenance!$C$3:$C$105<=$H$4),Maintenance!G$3:G$105)
D15	=SUMPRODUCT(--(Maintenance!$B$3:$B$105=$F$4),--(Maintenance!$C$3:$C$105>=$G$4),--(Maintenance!$C$3:$C$105<=$H$4),Maintenance!H$3:H$105)
J15	=B15/$H$8
K15	=C15/$H$8
L15	=D15/$H$8

Its always better not to use merged cells

 

[Sandeep Warrier]

To get the total maintenance cost in the 1st sheet, you can use a Sumproduct.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
2					Time Period in Question
3					Aircraft	From	To
4					C-172 FIIU	01/01/2009	01/03/2009
5
6					Hours Flown
7					Number of Days	Air Time	Flight Time
8					30
9
10
11	Cost of Maintenance
12
13	For The Given Time Period				Per Air Time Hour				Per Flight Time Hour
14	Parts	Labor	Total		Parts	Labor	Total		Parts	Labor	Total
15	$ 325.00	$ 476.00	$ 605.00
Tracking Info

Excel 2003

Cell Formulas
Range	Formula
F8	=H4-G4
F15	=IF(ISERROR($B$15/$G$8),"",$B$15/$G$8)
B15	=SUMPRODUCT(--(Maintenance!$B$3:$B$65536='Tracking Info'!F4),--(Maintenance!$C$3:$C$65536>='Tracking Info'!G4),--(Maintenance!$C$3:$C$65536<='Tracking Info'!H4),Maintenance!$F$3:$F$65536)
C15	=SUMPRODUCT(--(Maintenance!$B$3:$B$65536='Tracking Info'!F4),--(Maintenance!$C$3:$C$65536>='Tracking Info'!G4),--(Maintenance!$C$3:$C$65536<='Tracking Info'!H4),Maintenance!$G$3:$G$65536)
D15	=SUMPRODUCT(--(Maintenance!$B$3:$B$65536='Tracking Info'!F4),--(Maintenance!$C$3:$C$65536>='Tracking Info'!G4),--(Maintenance!$C$3:$C$65536<='Tracking Info'!H4),Maintenance!$H$3:$H$65536)
G15	=IF(ISERROR($C$15/$G$8),"",$C$15/$G$8)
H15	=IF(ISERROR($D$15/$G$8),"",$D$15/$G$8)
J15	=IF(ISERROR($B$15/$H$8),"",$B$15/$H$8)
K15	=IF(ISERROR($C$15/$H$8),"",$C$15/$H$8)
L15	=IF(ISERROR($D$15/$H$8),"",$D$15/$H$8)

The sheet that houses the flight hours as data according to months. How will you get the flight hours between specific periods?
The Per Air Time/Flight Time Hour would then be a simple division as you mentioned in your initial post.<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Pandy]

To get the total maintenance cost in the 1st sheet, you can use a Sumproduct.

The sheet that houses the flight hours as data according to months. How will you get the flight hours between specific periods?
The Per Air Time/Flight Time Hour would then be a simple division as you mentioned in your initial post.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

I was going to add them up @ the end of each month & input them on the 3rd Sheet.

As for the specific periods. I was thinking of using a circular reference