+ Reply to Thread
Results 1 to 7 of 7

Difference Between 2 Highest Figures when Filtering

  1. 04-22-2014, 03:13 AM #1
    jenna_max
    jenna_max is offline
    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
    Reply With QuoteRegister To Reply
  2. 04-22-2014, 05:00 AM #2
    cbatrody
    cbatrody is offline
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    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?
    Reply With QuoteRegister To Reply
  3. 04-22-2014, 05:56 AM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,393

    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" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 04-22-2014, 07:26 AM #4
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    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.
    Reply With QuoteRegister To Reply
  5. 04-22-2014, 07:57 PM #5
    jenna_max
    jenna_max is offline
    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.
    Reply With QuoteRegister To Reply
  6. 04-22-2014, 10:32 PM #6
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    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
    Reply With QuoteRegister To Reply
  7. 04-24-2014, 01:50 AM #7
    jenna_max
    jenna_max is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. [SOLVED] looking up figures in a column and selecting next highest match
    By [email protected] 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1