1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find highest value between given range

Discussion in 'Ask an Excel Question' started by Anbuselvam K, Jan 6, 2019.

  1. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    Hi

    I have numbers from 1 to 100, 1000 to 3000, and 4000 to 5000 in column B. I need the highest value from the number 1000 to 3000 in cell C5. Expected answer in cell C5 is 2999.

    Please find the attached sheet.

    Can anyone help me out?

    Thanks in Advance.

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    C5 :

    =AGGREGATE(14,6,B2:B30/(B2:B30>=1000)/(B2:B30<=3000),1)

    or,

    =MAX(INDEX(B2:B30*(B2:B30>=1000)*(B2:B30<=3000),0))

    Regards
    Bosco
  3. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    Thanks a lot, Bosco!
  4. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    Hi

    sometimes I need to enter a number in the format of "2097&2098&2099" in one cell. In this case, your suggested formula is not considering all three numbers to calculate the highest value.

    Can you please suggest another formula which can consider this kind of multiple entries too.

    Thanks in advance.

    Attached Files:

  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    upload_2019-1-6_21-24-19.png

    Some challenges in excel formula, but still can be done, please see :

    1] Define 3 range names as in :

    Name / Refers to

    InvoNum : =LEN(Sheet1!$B$2:$B$30)-LEN(SUBSTITUTE(Sheet1!$B$2:$B$30,"&",""))+1

    InvoRgn : =ROW(INDIRECT("1:"&SUMPRODUCT(LEN(Sheet1!$B$2:$B$30)-LEN(SUBSTITUTE(Sheet1!$B$2:$B$30,"&",""))+1)))

    SplitNumRgn : =0+TRIM(MID(SUBSTITUTE("&"&INDEX(Sheet1!$B$1:$B$30,AGGREGATE(15,6,ROW(Sheet1!$2:$30)/(InvoNum>=COLUMN(Sheet1!$A:$U)),InvoRgn)),"&",REPT(" ",99)),MOD(AGGREGATE(15,6,ROW(Sheet1!$1:$30)*100+COLUMN(Sheet1!$A:$U)/(InvoNum>=COLUMN(Sheet1!$A:$U)),InvoRgn),100)*99,99))

    Then,

    2] in C5, formula :

    =AGGREGATE(14,6,SplitNumRgn/(SplitNumRgn>=1000)/(SplitNumRgn<=3000),1)

    Regards
    Bosco

    Attached Files:

    Anbuselvam K likes this.
  6. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    Dear Bosco

    Thanks a lot for your suggested formula.

    In the attachment sheet 2, I have used below array formula in cell K7 to find out the missing number from given the minimum and maximum value in cell Q4 and R4, Note that this formula works without defining name ranges.

    {=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX($A:$A,$Q$4):INDEX($A:$A,$R$4)),SMALL(IFERROR(0+TRIM(MID(SUBSTITUTE(K8:K10895,"&",REPT(" ",30)),{1,30,60,90,120},30)),""),ROW(1:10895)),0)),ROW(INDEX($A:$A,$Q$4):INDEX($A:$A,$R$4))),ROW(A1)),"")}

    Problem is that excel taking much time to calculate if we define names. So can you suggest another formula like above which can find the highest numbers without name defining in cell R4?

    Thanks in Advance.

    Attached Files:

  7. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    Also, Please find the attached sheet which is having Date. I want to find the highest value with this year entry only. I think this will help you find the highest value without defining the name ranges.

    Attached Files:

  8. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    Can anyone suggest?
  9. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    What makes you think that the calculation time has anything to do with the use of defined names? You can always eliminate a named formula by substituting each occurrence by the formula it refers to. That is what the Excel calculation engine does (albeit with a small overhead) but, frankly, the chances of Excel performing the task correctly are far better than it would be involving any user/developer.

    Your problems start because of the way you have stored the concatenation of invoice numbers. That requires a search for the start and end of each number in the string. What should be done for computational efficiency is to store the resulting invoice number in a helper range so that they do not have to be recalculated every time the array is referenced. To perform the MAXIFS requires the array to be calculated several times (the function is actually performed as an AGGREGATION over the calculated array). You then perform the calculation a range in excess of 10,000 rows.

    It is hardly surprising that the calculation seems slow.
  10. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    Hi

    the below formula is finding the highest values

    =AGGREGATE(14,6,--MID(SUBSTITUTE(K8:K10921,"&",REPT(" ",30)),{1,30,60,90,120},30),1)

    But I need to get the results within the range of 4001 to 5000. I don't know how to include this range in the above formulation.

    Please suggest

    Thanks in advance.
  11. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    You already have the answer from @Bosco in post #5.
    = AGGREGATE( LARGE, IgnoreErrors,
    SplitNumRng/(SplitNumRng>4000)/(SplitNumRng<=5000), 1 )

    where LARGE:=14 and IgnoreErrors:=6 [I never could remember 'magic' numbers].

    If you insist you could substitute out the name SplitNumRng by replacing it with
    --MID(SUBSTITUTE(K8:K10921,"&",REPT(" ",30)),{1,30,60,90,120},30)
    but I assure you it will do nothing for you other than render your formula ridiculously long and unintelligible.
  12. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    Please Don't Cross-Posting.

    It is reported you have-posted in the following forum :

    https://www.excelforum.com/excel-general/1259228-find-the-highest-value-from-range-multiple-entry-and-adjacent-cells-date.

    •Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.

    •Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

    Regards
    Bosco
  13. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    What do you mean by cross-posting?

    I never post the same question with the different name and different section.

    It is my opinion to create many accounts in different forums to get a better solution.

    Till now I did not get the answer from any forums.

    it is not that your given answer is the final one. The world is So BIG Mr.Bosco...
  14. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    hi

    The below UDF code and steps are working fine.

    Function MaxSpecial(DateRng As Range, Year_ As Integer, NumRng As Range) As Long


    Dim cel As Range
    Dim T As Long, TA As Long


    T = 1
    For Each cel In NumRng


    If Year(DateRng.Cells(T, 1)) = Year_ Then
    M = Split(cel, "&")

    For TA = 0 To UBound(M)
    MaxSpecial = WorksheetFunction.Max(MaxSpecial, 0 + M(TA))
    Next TA

    End If
    T = T + 1
    Next cel


    End Function



    Steps:
    To paste the code
    Developer Tab --> Visual Basic
    Insert --> Module
    Then paste the code.
    Close the widow.
    Now UDF is available in function list.
    For DateRng Select J8:J9999
    For Year_ enter 2019 (Or required year)
    For NumRng Select K8:K9999
  15. rahulshewale1

    rahulshewale1 Active Member

    Messages:
    270
    Hii @anbuselvam,

    Could you please provide desire output by manually ?

    It can be achieve desire output via power query or VBA
    Regard

    Rahul shewale
  16. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    @Anbuselvam K
    Thank you for posting your macro. Where did it come from? Did you write it?

    Is the macro fast enough or would increased speed be of value?
    I have made some modification to your macro designed to increase speed but such optimisation is only relevant if the delay is long enough to be noticeable.

    Code (vb):
    Function MaxSpecial(DateRng As Range, Year_ As Integer, NumRng As Range) As Long
    Dim M
    Dim NumArr() As Variant
    Dim DateArr() As Variant
    Dim MaxNum As Long
    Dim T As Long, TA As Long
        NumArr = NumRng.Value
        DateArr = DateRng.Value
        For T = 1 To UBound(NumArr)
            If Year(DateArr(T, 1)) = Year_ Then
                M = Split(NumArr(T, 1), "&")
                For TA = 0 To UBound(M)
                    If M(TA) > MaxNum Then MaxNum = CLng(M(TA))
                Next TA
            End If
        Next T
        MaxSpecial = MaxNum
    End Function
    The gains are intended to come from the fact that exchanges between code an workbook cells tend to introduce a processing overhead which is reduced by block-reading ranges into variant arrays before processing. I have also avoided the worksheet function, replacing it by a comparison operator.
  17. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    It is a macro code suggested by another forum. please check the attached file for your information.

    Attached Files:

  18. rahulshewale1

    rahulshewale1 Active Member

    Messages:
    270
    hii @Anbuselvam K ,

    See if it is ok ?


    Regard
    Rahul shewale

    Attached Files:

  19. Anbuselvam K

    Anbuselvam K Member

    Messages:
    136
    Yes, it is also working fine. Thanks

Share This Page