Extracting data help

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
90
Office Version
  1. 2016
I have 2 tabs, one tab is my raw data. The other tab is my configured data. Here is a snap of my configured data:
MPG Report with Idle.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1JanFebMarAprMayJunJulAugSep
2Unit#MakeEngineMPGIdleMPGIdleMPGIdleMPGIdleMPGIdleMPGIdleMPGIdleMPGIdleMPGIdle
320000-20049Freightliner2016 Detroit DD156.1148.76.4237.8
420050-20099Freightliner2016 Detroit DD156.2644.86.0236.2
520100-20249Freightliner2017 Detroit DD156.7436.56.7434.36.9330.56.9827.56.9235.7
620250Freightliner2018 Detroit DD156.604.76.282.86.944.17.404.57.364.4
720251-20350Freightliner2018 Detroit DD156.8945.36.8841.17.1436.77.1739.16.7241.3
820351-20425Freightliner2018 Detroit DD156.9429.86.9928.97.2624.77.2927.66.8630.4
920426-20500Freightliner2018 Detroit DD136.89396.1240.37.1236.77.0639.67.0742.6
1020501-20625Freightliner2019 Detroit DD157.0735.77.15347.2934.77.3037.17.2541.9
1120626-20850Freightliner2019 Detroit DD137.0638.67.1236.37.3035.67.3138.57.3642.8
1220851-20875Freightliner2019 Detroit DD137.2027.17.2327.37.4226.57.4726.37.4734.0
1320876-20986Freightliner2020 Detroit DD137.3926.57.5124.7
14
1716700-16799International2018 Cummins6.4746.46.5844.16.7742.36.7447.06.7450.8
1816800-16899International2018 Cummins6.5546.96.6244.26.7143.16.8044.56.7949.0
1916900-16949International2019 Intl A266.6735.56.6735.36.8732.96.8036.86.6340.8
2016950-16981International2020 Intl A266.9629.06.9433.56.8039.8
21
22
23Freightliner2016 Detroit DD156.2246.26.1436.6
24Freightliner2017 Detroit DD156.7436.56.7434.36.9330.56.9827.5
25Freightliner2018 Detroit DD156.9138.36.9335.77.2031.27.2333.8
26Freightliner2018 Detroit DD136.8939.06.1240.37.1236.77.0639.6
27Freightliner2019 Detroit DD157.0735.77.1534.07.2934.77.3037.1
28Freightliner2019 Detroit DD137.0737.47.1335.47.3134.77.3337.3
29Freightliner2020 Detroit DD137.3926.5
30
31International2018 Cummins6.5146.66.6044.26.7542.76.7745.8
32International2019 Intl A266.6735.56.6735.36.8732.96.8036.8
33International2020 Intl A266.9629.06.9433.5
34
35International6.5544.46.6142.36.8039.36.7942.76.7446.3
36Freightliner6.9537.66.9136.07.2233.47.2334.87.0838.3
37Fleet6.8639.26.8437.17.1035.07.1136.96.9940.5
2020



Here is a sample of my raw data, which again is just a sample of it. As you can see in my configured data, there are "unit# ranges" so the unit numbers go from 16700 to 20986.
I need on the configured data tab in cell P5 to be the sum of the unit range's miles divided by the sum of the unit range's gallons (I already did this manually in the snapshot) (This range is located in cells A3 to A20)
I need on the configured data tab in cell Q5 to be the average of the unit range's idle percentage. (I already did this manually in the snapshot)

I'm guessing from there once I have a formula that works, I can plug in the unit # ranges for cells P6, P7, P8, etc...

Stracs May20.xlsx
ABCDEF
1UnitMilesHoursGallonsMPGIdle
2167009,449402:031,4166.6752.5%
31670110,910512:131,6236.7259.2%
41670212,788280:391,6387.8111.2%
5167039,494315:111,3866.8538.9%
61670412,182717:101,9406.2867.8%
71670517,536505:342,4227.2431.9%
8167068,790570:441,3756.3968.5%
9167076,727258:211,0316.5347.7%
10167087,287257:359407.7540.0%
11167098006:2671.080.0%
12167108,762448:191,2307.1359.4%
13167118,154455:121,1786.9265.5%
14167128,373414:461,3876.0458.8%
15167134,996295:168306.0263.9%
161671411,617339:081,6217.1732.1%
17167155,003423:299035.5472.6%
18167167,207442:291,1436.3168.4%
191671716,114643:002,2737.0951.3%
20167187,164202:339687.4024.3%
21167199,012478:411,4016.4361.1%
221672012,357267:441,7447.0811.0%
231672111,286261:041,4847.6020.7%
241672211,037528:271,6736.6057.9%
25167238,567292:541,1707.3242.5%
26167246,548234:419267.0740.2%
27167258,952457:191,4126.3459.6%
28167264,312321:267305.9173.4%
291672710,231668:431,5756.5070.1%
301672818,144708:582,5007.2651.3%
31167291000:2511.330.0%
32167307,967169:319878.076.5%
33167318,471480:321,3016.5166.1%
34167329,924436:201,4326.9356.7%
35167338,220544:441,4265.7766.9%
36167346,867391:041,0906.3060.9%
371673512,020504:221,8126.6354.5%
38167369,780555:291,4956.5462.6%
39167378,762378:481,2946.7755.4%
40167389,285308:501,2447.4636.8%
41167396,291323:129896.3662.2%
421674013,488298:531,8267.399.6%
43167418,748319:381,2746.8741.8%
44167424,939203:026817.2549.5%
45167438,956313:321,1727.6440.3%
46167449,392254:181,3916.7530.1%
47167455,211153:146607.8933.2%
481674610,115564:131,6836.0164.6%
491674710,889306:491,4797.3631.0%
50167489,021488:381,4406.2664.5%
51167498,923502:341,4156.3165.2%
521675010,748316:431,4057.6528.4%
53167519,197264:301,2567.3222.8%
54167527,287223:169137.9829.4%
Make
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
90
Office Version
  1. 2016
Maybe I made it sound complicated? Just need a formula that allows me to alter the "range" of the raw data. So for instance:
In a cell, (let's say M2), I need a formula which range is 16700 - 16750, sum up column B for that range, then divide that by the sum of column D for the same range to get my answer.

Then in another cell,(let's say M3), I need that range (16700 - 16750) and for it to find the average of column F.

Likewise, for all my other cells, I can use that formula and just manually adjust what I want my range to be once I have a working formula.
 

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
90
Office Version
  1. 2016
=sumifs function GOT IT!

Sometimes I stress too fast and post before thinking.... :rolleyes:
 

Watch MrExcel Video

Forum statistics

Threads
1,114,531
Messages
5,548,586
Members
410,854
Latest member
ajbaluyut
Top