$20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF

    Hi there,


    This has already been posted in the free section, with no hits, so just linking back to original post for reference: http://www.ozgrid.com/forum/showthread.php?t=170361


    I am aware of how to accomplish a seudo "minif's"/"maxif's" calculation using an array formula and nested if statements. However, I work with a few other people in the office who are not as familiar or comfortable with this type of formula, but they could benefit from the functionality of having a simplified UDF where they could pass in criteria and criteria ranges (guessing with a ParamArray as the number of criteria would have to be flexible). Also, would need to have the comparitive logic be flexible in the sense that one argument could be where the range is A:A, and the criteria is = value in a cell (i.e. B2) OR same range, but criteria is <> 0.

    Also what would be helpful is the ability to declare the return range to be optionally different than the lookup range. I.e. If I have a list of companys in column A, and a list of prices in column B, the option to either return the min/max value, OR the corresponding company in column A for the min value would be great.

    And...now that I'm putting this all on paper, the ability to use Small/large, rather than Min/max would be preferrable, as occasionally the user may want the nth smallest/largest return etc. So being able to select what small/large value as an argument (i.e. return 1st small, 2nd small etc.).

    Thanks,

    Re: $20 USD - minifS/maxifS UDF


    So far as I can remember, I have already wrote those "IFS" UDF functions for another solution. So SMC if you dont want it, I think I have something pre-written...

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: $20 USD - minifS/maxifS UDF


    Thanks SMC...


    Powera86, here you go friend. Let me know if this meets your criteria (I believe it does reference/match everything you asked for....)


    So this new IFS function is called "MinMaxIFS"... This one function can return either the Minimum or Maximum value from a column of Data, that meets a set of crtieria which you define (exactly like SUMIFS, COUNTIFS and AVERAGEIFS).

    It is called from the worksheet thus:
    =MinMaxIFS([Min or Max Number], Range to check for Min or Max, Criteria1 Range, Criteria1, Criteria2 Range, Criteria 2 etc. etc. etc.)

    The [Min Or Max Number] can either be 1 or 2. If set to 1, the minimum is returned. If set to 2 the maximum is returned.
    e.g.
    =MinMaxIFS(1,B1:B200,C1:C200,">200",D1:D200,"<100")
    returns the minimum value in Range B1 to B200 where C1 to C200 is greater than 200 and D1 to D200 is less than 100.

    Calling MinMaxIFS without any criteria just returns the Minimum or Maximum of the range specified...e.g.
    =MinMaxIFS(2,B1:B200)
    returns the maximum of B1 to B200.

    As you will discover from working with Excel "IFS" functions the criteria range MUST match the size/shape of the range that it will be operating on, so calling an IFS function like this:
    =MinMaxIFS(2,B1:B200,C2:C99,"<10")
    is not valid as C2:C99 is a different size to B1 to B200.

    In the cases where an error occurs in the function, or the wrong number of arguments is sent into the function, the function errors out and returns zero to the cell.

    Finally - in the attachment, the "Sample IFS" tab has some sample data, with a couple of MinMaxIFS formulas to show how they work.

    I hope you find this useful to your needs.

    Regards,
    Ger

    Files

    Min and Max IFS UDF function.xlsm 86.56 kB – 95 Downloads

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: $20 USD - minifS/maxifS UDF


    Hi Ger Plante,


    I have reviewed your attached and thank you for taking the time. However, I have either missed, or it is not present...the ability to designate the return range (i.e. to your attached example, maybe I want to search for the min cost per tonne, where apples is the product, and instead of returning the min value, I want to return the corresponding value from the "Consumers" field.


    Also, I don't see the option to return the nth smallest, or largest value. I.e. perhaps I want info for the second smallest value in the range, or the 2nd largest etc.

    Re: $20 USD - minifS/maxifS UDF


    Hey Powera86...


    Well its a tough call. Its best not to wrap too many functions/features into one UDF... it'll confuse the hell out of users. And also the developer :)


    Here's I would approach it. Leave the MinMaxIFS function as-is, because it works :)


    I wrote a new SmallLarge IFS UDF function (called SmallLargeIFS) - it works the same way as MinMaxIFS... here's how.
    =SmallLargeIFS([Small or Large Number], Range to check for Small or Large, Nth Smallest or Nth Largest, Criteria1 Range, Criteria1, Criteria2 Range, Criteria 2 etc. etc. etc.)


    So if you want to find Small numbers, you call it with "1". If you want to find Large numbers you call the UDF with "2".


    For example:
    =SmallLargeIFS(1,A1:A10,3) ---> 3rd smallest number in A1:10
    =SmallLargeIFS(2,A1:A10,8) ---> 8th largest number in A1:10
    =SmallLargeIFS(2,A1:A10,8,B1:B10,">500") ----> 8th largest number in A1:10 where B1:B10 is greater than 500
    etc. etc. etc. keep adding criteria as you need.


    In relation to specifying what information to return - that complicates matters significantly, and I handle it by wrapping the UDF functions above in an INDEX/MATCH lookup.
    So =SmallLargeIFS(1,C1:C10,3) finds the third smallest value in C1:C10... this might return "50" for example. Use an exact match function to find that 50 in C1:C10 --->
    =MATCH(SmallLargeIFS(1,C1:C10,3),C1:C10,0) ---> returns the row number of the 3rd smallest number in C1:C10
    =INDEX(A1:A10,MATCH(SmallLargeIFS(1,C1:C10,3),C1:C10,0)) ---> returns the value in Column A for corresponding to the 3rd smallest number in C1:C10


    More examples, and code attached.


    Ger

    Re: $20 USD - minifS/maxifS UDF


    Thanks Ger,


    Little tied up today with finalizing some month end reporting. Will take a look over the weekend and make sure everything is in-line with where I need it to be.


    Are you ok to wait til Monday

    Re: $20 USD - minifS/maxifS UDF


    Thats fine - no problem. Talk then!

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: $20 USD - minifS/maxifS UDF


    Cool... Private Message Sent with Paypal details.

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF


    Received!!! :) Thx.

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF


    Ger,


    There seems to be a bit of an issue with your Index/Match solution for returning a corresponding value to a small/large value etc. If there are two "Products" that have the same "cost per tonne", but one of them satisfies the criteria, and one doesn't, the index/match will always return the product that comes first in the list, rather than the product that has the min value but also satisfies the criteria.


    Any way around this?

    Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF


    Hi Powera86,


    So If I understand correctly, you might have two "hits" with the same value. In that case, yes Index/match combo will return the first instance only.


    So if possible, add extra criteria to eliminate possible duplicate records. If that is not possible consider this UDF from Dave Hawley which would replace / eliminate the need for the Index/Match functions.


    Using the result of "SmallLarge" udf (or whatever UDF), plug them into this UDF:
    http://www.ozgrid.com/VBA/TwoColLkUp.htm


    This will look up a value in a column in a table and return the nth value in a different column. It will do the same thing as Index/Match, but has the benefit of returning the nth occurence for duplicates.


    Regards
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF


    Ger,


    Unless I'm missing something (which is totally possible, haven't had my morning coffee yet), this still doesn't provide a solution to my problem. Even being able to select the Nth occurence of the lookup return, I am still not returning the correct number. Who's to say what occurence in the list is the correct one? If I have three products, all with the same cost per tonne, but the first and second occurences don't satisify the criteria that I laid out in the udf's you provided, then obviously I would want the third occurence of the value. But there's no way to know that, and from what I can tell certainly know way to code that with what you have provided so far.


    I know I have sent payment already, I really should have tested this further as this does not satisfy my original request and really only leaves me with half a solution.

    Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF


    Hi powera87, I think the problem is, I dont think I understand your problem :)


    Can you send me an example spreadsheet by email showing how/where the solution doesnt fit your requirement. You can email it to me at the address I gave you before if it is confidential.


    Just in relation to your comment here:

    Quote

    Who's to say what occurrence in the list is the correct one?.... But there's no way to know that....

    As a general rule of thumb if you cant define this as a consistent Rule, then I wont be able to code it. ;) In other words, how do you know if it is the first, second or third occurrence that you need to return.


    Cheers
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF


    Hi Ger,


    No worries, I will try and explain a little better.


    Take your example workbook that you sent to me in post #7 of this thread. In row 7 where you have the example "Product for the Minimum cost per Tonne where there are more than 10,000 consumers and less than 5,000 retailers". Try changing the criteria so that one of the contraints is there are > 500,000 consumers (i.e. =INDEX(A:A,MATCH(MINmaxifs(1,D1:D30,B1:B30,">500000",C1:C30,"<5000"),D:D,0))).


    The formula is returning Product "D", because...there is a Product that fits the contraints and has a min cost per tonne value of 215 (i.e. Product "V"), and D happens to fall on the list before V does...even though the criteria are not satisfied for Product D as the consumers are less than 500,000.


    The issue is, if there are two products that have the same min value, but one satisfies the criteria, and one doesn't, it will ignore the criteria and just pick the first one on the list, which, isn't what I need for obvious reasons.


    I hope that makes more sense?

    Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF


    I see what youre saying, OK, gimme a second to think about a solution :)

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF


    OK, that was a long second... :D


    I added an extra parameter to each UDF... this specifies the RELATIVE column number to what you are calculating on... so this effectively removes the requirement for Index/match. So the parameters are now:


    =MinMaxIFS(Min_or_Max, Range, Rel_Col, Criteria_Range1, Criteria, Criteria_Range2, Criteria etc. etc.)
    Where:
    Min_or_Max = 1 for Minimum and 2 for Maximum
    Range = the range on which you want to calculate the Min or maximum value on
    Rel_Col = relative column number, RELATIVE to the Range above on which you want to return the value. Specify negative values for columns to the left of range, zero for the same column as the range and positive numbers for columns to the right of range.
    Criteria_Range1 = the range on which you want define some criteria
    Criteria = the actual criteria you want to apply to the criteria range
    etc.


    SmallLargeIFS has the relative column criteria also:
    =SmallLargeIFS(Small_Or_Large, Range, Nth, Rel_Col, Criteria_Range1, Criteria, Criteria_Range2, Criteria etc. etc.)
    Where:
    Small_or_Large = 1 for Small and 2 for Large
    Range = the range on which you want to Find the Nth Smalles or Nth Largest value on
    Nth = the Nth smallest or largest value
    Rel_Col = relative column number, RELATIVE to the Range above on which you want to return the value. Specify negative values for columns to the left of range, zero for the same column as the range and positive numbers for columns to the right of range.
    Criteria_Range1 = the range on which you want define some criteria
    Criteria = the actual criteria you want to apply to the criteria range
    etc.


    I updated the examples from Post #7, and highlighted the troublesome row#7 which now returns the correct Product.


    Let me know if you have any questions.


    Ger

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login