• 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...

  • 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

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.
 

Attachments

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.
 

Attachments

bosco_yip

Excel Ninja
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.
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
 

Attachments

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.
 

Attachments

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.
 

Attachments

Peter Bartholomew

Well-Known Member
Problem is that excel taking much time to calculate if we define names.
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.
 
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.
 

Peter Bartholomew

Well-Known Member
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.
 

bosco_yip

Excel Ninja
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.
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
 
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...
 
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
 

rahulshewale1

Active Member
Hii @anbuselvam,

Could you please provide desire output by manually ?

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

Rahul shewale
 

Peter Bartholomew

Well-Known Member
@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:
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.
 
Top