+ Reply to Thread
Results 1 to 3 of 3

Formula work when i use Subtotal(5, but wont work when i use Min. Y? WHats the difference?

  1. #1
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    424

    Formula work when i use Subtotal(5, but wont work when i use Min. Y? WHats the difference?

    I have a sample data attached. I use formula to count the no of times each column appear as the smallest with its row. Example A1:C1.

    I manage to get the formula in A8:C8 using sumproduct and subtotal. However, i tried to change the subtotal to Min and it wont work. Why is it so? Aint they suppose to be the same????
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,434

    Re: Formula work when i use Subtotal(5, but wont work when i use Min. Y? WHats the differe

    Sorry, I really dont understand what that formula is supposed to be doing?
    =SUMPRODUCT(--(A1:A6=SUBTOTAL(5,OFFSET($A$1,ROW(A1:A6)-ROW(A1),,1,3))))
    What does 2 (or 1 or 3) represent?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    424

    Re: Formula work when i use Subtotal(5, but wont work when i use Min. Y? WHats the differe

    Hi, its suppose to count how many times the column is the MIN within the row of 3. I have put conditional formatting to highlight the Lowest within the Row (example, A1 has value of 104 and is the min in A1:C1.
    Hence the 2 you see in A8 is A1 and A6 where they are the min. The 1 in B8 come from B2. The 3 in C8 come from C3, C4 and C5.
    Attached Files Attached Files

+ 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. [SOLVED] Baffled why formula wont work
    By dougmorgan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2016, 11:25 AM
  2. [SOLVED] Formula in one work sheet wont accept result from another in a different work sheet.
    By Belinea2010 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-07-2015, 12:24 PM
  3. [SOLVED] Conditional Formatting wont work when I am using a formula
    By Jojojojo7 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-23-2015, 08:13 AM
  4. IF Statment wont work due to =NOW() formula
    By Muzza86 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-16-2014, 09:23 AM
  5. Excel formula wont work
    By thepitzaboy in forum Excel General
    Replies: 2
    Last Post: 07-08-2011, 02:31 AM
  6. Why wont max work on cells that have a formula
    By leedsexcel in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 11-23-2007, 02:31 PM
  7. Why wont this simple formula work
    By Bigredno8 in forum Excel General
    Replies: 3
    Last Post: 12-06-2005, 11:30 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