Kronossaurus
New Member
- Joined
- Jan 19, 2012
- Messages
- 2
Well hello everyone,
This is my first Post , so please be gentle.
I need some help with an excell workbook file that i´m making.
Basically it´s for heavy machinery and 2 control the gas consumption, il try and walk you trough it.
First sheet : Month - this is where we manually input all the information from workers, consisting of the . Date . Shift ( we have 3 eight hour shifts) . Area (To whom the equipment belongs 2 ) . Internal code of the machine . Time of the fill up . Engine Hours . Liters of gas . Pump . and the Gas Key(this is for trying to see if the guys are using the corrects keys ).
Second sheet: All of the data outputted from the work site, all the electronical information basically, consisting almost of the same data.
Third: Daily consumption - Here i tried a simple sumif by area to get the total comsuption per day per work área , also acumulated consumption, and daily average.
Fourth: Daily comparison sheet - consists of two pivot tables, where i try to control and compare the data between the operators and the daily fuel pump output, because well workers tend to forget to write down , and so far this is the way i found to compare them.
Rest of the sheets: Machines - (Linked to the first sheet)
so i guess this would be my main problematic area, i created a sheet for each work area, in that file i only have one as an example. Has the date, internal codes segmented . It´s where i´m trying 2 get the monthly average and daily averages, daily averages are important 2 me 2 see if theres any abnormal comsuption ( either broken pumps or gas stealing)
- Acumulated consumption , sums the daily consumptions .
- Worked hours, i tried this with the minimum work hours and the max work hours.
- Daily liters, sum per day
- minimum/max hours, well each machine can fuel up several times a day , so i tried and used a minimum and maximum engine hours to try and get the worked hours per day , now the problem here is if the machine only fuels up once, the minimunm and maximum are the same so i cant relly get a work hour out of it.
- Hours day - explained above, because im trying daily averages i try and do this as the minimum from one day and the maximum of the following day.
- Daily averages, here is my biggest problem i think , basically just a simple division between the fuel intake and the worked hours.
Now it kinda works fine when the machines work all the days in a row, but when he skips a day , because the worked hours is considering the previous and following day engine hours, it doesnt actually give me a daily consuption on those days, (some one could just steal fuel and i wouldnt notice it on the file).
So i kinda needed 2 know if theres some way of getting it to if the value of the next day is 0 it copys the precvious day? or if i could get a way of only copying the data that actually has some input in the First sheet.
I´m really strugling this this specific function , on how the best way of doing this would be, any sugestion or alteration would be highly apreciated, also if you remember some other way of achieving the same goal or some tweak i could use in other sheets.
I think i translated mostly everything on the worksheet, il anex it here:
https://feupload.fe.up.pt/get/xU6TS5QsJhTKlOG
Sorry for the long post and thank you in advanced!
This is my first Post , so please be gentle.
I need some help with an excell workbook file that i´m making.
Basically it´s for heavy machinery and 2 control the gas consumption, il try and walk you trough it.
First sheet : Month - this is where we manually input all the information from workers, consisting of the . Date . Shift ( we have 3 eight hour shifts) . Area (To whom the equipment belongs 2 ) . Internal code of the machine . Time of the fill up . Engine Hours . Liters of gas . Pump . and the Gas Key(this is for trying to see if the guys are using the corrects keys ).
Second sheet: All of the data outputted from the work site, all the electronical information basically, consisting almost of the same data.
Third: Daily consumption - Here i tried a simple sumif by area to get the total comsuption per day per work área , also acumulated consumption, and daily average.
Fourth: Daily comparison sheet - consists of two pivot tables, where i try to control and compare the data between the operators and the daily fuel pump output, because well workers tend to forget to write down , and so far this is the way i found to compare them.
Rest of the sheets: Machines - (Linked to the first sheet)
so i guess this would be my main problematic area, i created a sheet for each work area, in that file i only have one as an example. Has the date, internal codes segmented . It´s where i´m trying 2 get the monthly average and daily averages, daily averages are important 2 me 2 see if theres any abnormal comsuption ( either broken pumps or gas stealing)
- Acumulated consumption , sums the daily consumptions .
- Worked hours, i tried this with the minimum work hours and the max work hours.
- Daily liters, sum per day
- minimum/max hours, well each machine can fuel up several times a day , so i tried and used a minimum and maximum engine hours to try and get the worked hours per day , now the problem here is if the machine only fuels up once, the minimunm and maximum are the same so i cant relly get a work hour out of it.
- Hours day - explained above, because im trying daily averages i try and do this as the minimum from one day and the maximum of the following day.
- Daily averages, here is my biggest problem i think , basically just a simple division between the fuel intake and the worked hours.
Now it kinda works fine when the machines work all the days in a row, but when he skips a day , because the worked hours is considering the previous and following day engine hours, it doesnt actually give me a daily consuption on those days, (some one could just steal fuel and i wouldnt notice it on the file).
So i kinda needed 2 know if theres some way of getting it to if the value of the next day is 0 it copys the precvious day? or if i could get a way of only copying the data that actually has some input in the First sheet.
I´m really strugling this this specific function , on how the best way of doing this would be, any sugestion or alteration would be highly apreciated, also if you remember some other way of achieving the same goal or some tweak i could use in other sheets.
I think i translated mostly everything on the worksheet, il anex it here:
https://feupload.fe.up.pt/get/xU6TS5QsJhTKlOG
Sorry for the long post and thank you in advanced!
