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

P

Pandy

New Member
Joined
Jan 15, 2009
Messages
9
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
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).
 
Last edited:
Upvote 0
try this
Excel Workbook
ABCDEFGHIJKLM
1
2Time Period in Question
3AircraftFromTo
4C-172 FIIU01/01/200831/01/2008
5
6Hours Flown
7Number of DaysAir TimeFlight Time
8305560
9
10
11Cost of Maintenance
12
13For The Given Time PeriodPer Air Time HourPer Flight Time Hour
14PartsLaborTotalPartsLaborTotalPartsLaborTotal
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
RangeFormula
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
 
Upvote 0
To get the total maintenance cost in the 1st sheet, you can use a Sumproduct.
Excel Workbook
BCDEFGHIJKL
2Time Period in Question
3AircraftFromTo
4C-172 FIIU01/01/200901/03/2009
5
6Hours Flown
7Number of DaysAir TimeFlight Time
830
9
10
11Cost of Maintenance
12
13For The Given Time PeriodPer Air Time HourPer Flight Time Hour
14PartsLaborTotalPartsLaborTotalPartsLaborTotal
15$ 325.00$ 476.00$ 605.00
Tracking Info
Excel 2003
Cell Formulas
RangeFormula
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">
 
Upvote 0
sandeep.warrier said:
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">
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
How to have Excel determine the correct Sales Tax to use.
Replies
6
Views
3K
Joe4
Joe4
J
  • Question Question
Anyway to use OFFSET & MATCH together?
Replies
1
Views
3K
JadeWitte
J
M
  • Question Question
What do I need to read up on to solve this problem?
Replies
3
Views
472
mr_crowley
M

Forum statistics

Threads
1,223,914
Messages
6,175,351
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top