+ Reply to Thread
Results 1 to 5 of 5

I am struggling to understand what this formula is doing?

  1. #1
    Forum Contributor
    Join Date
    06-14-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    180

    I am struggling to understand what this formula is doing?

    Hi,

    I am struggling to understand what this formula is doing
    Please Login or Register  to view this content.
    Can someone please explain if you know?
    Is there a much more simple way of doing what it is trying to do?

    Thanks,

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,929

    Re: I am struggling to understand what this formula is doing?

    Maybe practice with =maxifs()

  3. #3
    Forum Contributor
    Join Date
    06-14-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    180

    Re: I am struggling to understand what this formula is doing?

    this is pretty urgent so won't have time to practice but I'll give it a go.
    Is the current formula replaceable using MaxIfs?

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: I am struggling to understand what this formula is doing?

    First, you cannot use MAXIFS if you are truly using Excel 2010, as your profile states.

    Second, in Excel 2010, a slightly simpler syntax would be:

    { =MAX(IF($A$1:$A$4543=A4, IF($G$1:$G$4543<G4, $G$1:$G$4543))) }

    You do not type the curly braces. Instead, you must be careful to array-enter the formula by pressing ctrl+shift+Enter instead just Enter after typing the formula =MAX(...) .

    (And note that the left curly brace is left of "=", not right of it as you wrote.)

    To answer your question (understand the formula), the formula calculates the max of only those values in G1:G4543 in rows where the corresponding value in A1:A4543 equals A4 __and__ the value in G1:G4543 is less than G4.

    Since A4 and G4 are relative references, they will change to A5 and G5, A6 and G6 etc as the formula is copied down a column.

    We can __not__ use simpler syntax like =MAX(IF(AND($A$1:$A$4543=A4,$G$1:$G$4543<G4),$G$1:$G$4543)) in a normally-entered (or array-entered) formula. It will "work"; read: not produce an error. But it will not select from G1:G4543 based on a row-by-row AND of the conditions, as intended. So it usually returns the wrong value, except by coincidence.

    If you can use =MAXIFS($G$1:$G$4543, $A$1:$A$4543, A4, $G$1:$G$4543, "<"&G4) , you are using a later version of Excel than Excel 2010. You should update your profile, or at least mention the Excel version in your posting.
    Last edited by joeu2004; 09-10-2020 at 11:05 AM.

  5. #5
    Forum Contributor
    Join Date
    06-14-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    180

    Re: I am struggling to understand what this formula is doing?

    joeu2004 - thank you, that is a brilliant and a very helpful explanation. Much appreciated.

+ 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] Struggling with Calculation formula Please help!!
    By Pankaj jaswani in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2020, 04:28 AM
  2. Struggling with a Time Formula
    By EDMONDO66 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2018, 03:40 PM
  3. [SOLVED] Struggling with excel formula
    By olegmcnoleg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-13-2014, 06:37 AM
  4. [SOLVED] Struggling with COUNTIFS formula - help!
    By Joey_997 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2014, 10:54 AM
  5. struggling with lastrow formula
    By skalaima in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-16-2013, 02:05 PM
  6. [SOLVED] Struggling to understand the Index/Match functions
    By PosiJoel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-26-2012, 08:57 PM
  7. Replies: 1
    Last Post: 09-18-2007, 02:07 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