+ Reply to Thread
Results 1 to 7 of 7

Difference Between 2 Highest Figures when Filtering

  1. #1
    Registered User
    Join Date
    04-22-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    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. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    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. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    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. ;-)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    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.
    Attached Files Attached Files
    Last edited by sktneer; 04-22-2014 at 07:29 AM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    04-22-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Difference Between 2 Highest Figures when Filtering

    Fuel_spread.xlsx

    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. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-22-2014
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Extract Highest and Second Highest Figures
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-26-2013, 06:32 PM
  2. Sorting a column of figures into highest to lowest
    By josand in forum Excel General
    Replies: 4
    Last Post: 10-02-2012, 09:53 AM
  3. Highest figures
    By spoter in forum Excel General
    Replies: 7
    Last Post: 07-07-2009, 08:05 AM
  4. looking up figures in a column and selecting next highest match
    By jane-rawlins@beeb.net in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-03-2005, 09:05 PM

Bookmarks

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