# Difference Between 2 Highest Figures when Filtering

1. ## Difference Between 2 Highest Figures when Filtering

Hi, I have a spreadsheet where I am recording fuel usage for a fleet of vehicles. I am recording, date, vehicles and odometer readings. I also have a filter in place.

When I filter the spreadsheet to show me information for a specific vehicle, I want a formula which will calculate the difference in KM between the last 2 fuel fill ups. I have used the SUBTOTAL function to determine the MAX and MIN figures. Is there a way of using the SUBTOTAL function for this too?

I have used the formulas LARGE(range,1) and LARGE(range,2) to determine the 2 highest KM Figures, but they do not change when I filter for a specific vehicle, which is what I want it to do so I can determine how many KM the vehicle has travelled between filling up with fuel.

Below is a sample of the spread sheet, if that helps anyone.

Date Vehicle KM's
25/02/2014 LV1 243,573
25/02/2014 LV5 139,876
25/02/2014 LV1 244,263
27/02/2014 LV10 44,099
01/03/2014 LV6 365,399
02/03/2014 LV1 244,850
05/03/2014 LV4 115,328
06/03/2014 LV1 245,326
06/03/2014 LV10 44,631
08/03/2014 LV10 44,993
10/03/2014 LV4 116,639
10/03/2014 LV10 45,558
13/03/2014 LV1 246,665

Any help will be greatly appreciated

2. ## Re: Difference Between 2 Highest Figures when Filtering

Hi,

This can be done using Pivot Table, can you please upload the above information in excel format?

3. ## Re: Difference Between 2 Highest Figures when Filtering

@cbatrody

Copy and paste the list into a worksheet and use the data column to columns feature to get the three columns using space as a separator: takes less than 10 seconds. ;-)

4. ## Re: Difference Between 2 Highest Figures when Filtering

What you are trying to do, can be achieved with the help of formulas. Find the attached sheet to see if this fulfills your requirement. The attached workbook has two sheets "Data" and "Filter". The data sheet contains your raw data and in filter sheet you can filter data with the help of formulas. The formulas in the filter sheet are such that if you input more data in Data sheet, it will be reflected in the filter sheet. You can increase the range in the formula if needed in future.
Hope this helps.

5. ## Re: Difference Between 2 Highest Figures when Filtering

I have attached a copy of the worksheet I already have. The attachment was exactly what I wanted How can I get the information I have here to transfer to that? What are the steps? Is it in Advanced Filter that I go? There is more information I need to add such as rego numbers, charge dockets etc.

6. ## Re: Difference Between 2 Highest Figures when Filtering

Find the attached workbook.

As I said this can be achieved with the help of formulas.
On the Filter sheet, there is a drop down list in cell B2 to select vehicle type.
The workbook has total four formulas on Filter sheet. One Array Formula in cell A5 and three regular formulas in the cell H5, J5 and L5 for MAX, MIN and AVERAGE respectively.
Steps to apply these formulas on Filter sheet are as follows...

On Filter sheet......

In cell A5
Copy the formula given below --> Select cell A5 -->Press F2 (function key)--> Ctrl+V to paste the formula --> hold down the Ctrl + Shift and then press Enter. Grab the fill handle of cell A5 (bottom right corner of cell boudary of A5) and drag it up to E5 --> While A5:E5 range is still selected, grab the fill handle of cell E5 (bottom right corner of cell E5) and drag down up to E100.
This way the array formula will be applied to the range A5:E100. Now format the col. A as Date. The array formula is.....

``Please Login or Register  to view this content.``

In cell H5, place the formula given below (For MAX)

``Please Login or Register  to view this content.``
and drag down to H6.

In cell J5, place the formula given below (For MIN)

``Please Login or Register  to view this content.``
and drag down to J6

In cell L5, place the formula given below

``Please Login or Register  to view this content.``
And that's it.

Hope this helps.

7. ## Re: Difference Between 2 Highest Figures when Filtering

Is there a way this formula can be simplified even further. in a way that I can just input fields for any spreadsheet? As I am having trouble getting the filter list on my second spreadsheet as well
i.e. =IFERROR(range(Sheet(range))function)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1