+ Reply to Thread
Results 1 to 10 of 10

Max Ifs Formula? is this possible

  1. #1
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Max Ifs Formula? is this possible

    Hi

    im trying to create a "MAX IFS" formula, and am currently using this....

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But this doesnt return the right value, is there a way to create a maxifs, like you would wth a SUMIFS?

    Dan

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Max Ifs Formula? is this possible

    for example
    =MAX(IF(A1:A100="YES",B1:B100,0)*(IF(C1:C100="YES",1,0))
    as an array formula, confirmed with ctrl+shift+enter

  3. #3
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: Max Ifs Formula? is this possible

    Hi, yes but it depends on multiple conditions

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Max Ifs Formula? is this possible

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: Max Ifs Formula? is this possible

    Ah right got you thank you

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Max Ifs Formula? is this possible

    If you use whole columns the formula may be very slow - try this version of your formula with 1000 rows, adjust ranges as required

    =IF(OR(D41=4,D29=2),0,MAX(IF(('High,Low,Avg'!$D2:$D1000='Supply Chain Calculator'!$D$30)*('High,Low,Avg'!$H2:$H1000='Supply Chain Calculator'!$D$38)*ISNUMBER(MATCH('High,Low,Avg'!$B2:$B1000,INDEX($A$230:$BI$230,,'Supply Chain Calculator'!$D$31):INDEX($A$230:$BI$230,,'Supply Chain Calculator'!$D$32),0)),'High,Low,Avg'!$U2:$U1000))+D48)

    confirmed with CTRL+SHIFT+ENTER

    For the last condition I assume that column B just has to match any value in the range created by your INDEX functions....and 'High,Low,Avg'!$U2:$U1000 is the range from which the MAX value should come?
    Audere est facere

  7. #7
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: Max Ifs Formula? is this possible

    Yes thats right, i want to look at the max between two dates specifically so the dates are labelled by week numbers 1 through to 52, my index in cells a230 - bi230 is then 1-52, so hopefully it should only look at the max in that selection....

    I will try your formula thank you

  8. #8
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: Max Ifs Formula? is this possible

    Thats perfect thank you very much!!!

    I had just written out.....

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But was a long way off being close to your formula.

    Thanks again Dan

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Max Ifs Formula? is this possible

    OK so 'Supply Chain Calculator'!$D$31 and 'Supply Chain Calculator'!$D$32 are the start and end weeks of the range you want to look at and 'High,Low,Avg'!$B:$B contains the week numbers? You can probably simplify a little like this:

    =IF(OR(D41=4,D29=2),0,MAX(IF(('High,Low,Avg'!$D2:$D1000='Supply Chain Calculator'!$D$30)*('High,Low,Avg'!$H2:$H1000='Supply Chain Calculator'!$D$38)*('High,Low,Avg'!$B2:$B1000>='Supply Chain Calculator'!$D$31)*('High,Low,Avg'!$B2:$B1000<='Supply Chain Calculator'!$D$32),'High,Low,Avg'!$U2:$U1000))+D48)

    You say the week numbers 1 to 52 are in A230:bI230......but that's a range of 61 cells?
    Last edited by daddylonglegs; 12-20-2013 at 08:31 AM.

  10. #10
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: Max Ifs Formula? is this possible

    Sorry the week numbers are 57 long, i had to do it that way as its looks at two countrys which have different rates and different arrival dates so the common factor was week commencing, but looked at arrival date to figure that out,

    The data starts from E sorry not A

+ 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. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Tags for this Thread

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