• 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 Max in Range

G

GB

Member
Hi,

I am using the following formula =IF(A10>MAX(A2:A19),A10,"") and copying down 500,000 rows. I would like to know a more efficient way of achieving this result using a non/semi volatile array entered formula.


Thanks in advance

GB
 
Hi ,


I do not know what you wish to do ; probably if you can explain that , you can get a solution quicker.


MAX(A2:A19) will return the maximum value in the range A2:A19.


You are then comparing this maximum value with A10 ; A10 is within the range A2:A19 ; even if A10 were the maximum value within the range A2:A19 , it could not be greater than itself. Which means the test A10>MAX(A2:A19) will always return FALSE.


It is a complicated way to fill up 500,000 cells with blanks.


Narayan
 
Hi Narayan,

yes you are correct, what I mean't was..... all my data is in column A. I want to evaluate if cell A10 is greater than A2:A9 & A11:A19, then moving down to cell A11 (A eleven) I want evaluate if cell A11 is greater than A3:A10 & A12:A20 and so on. Hope this helps.

cheers

GB
 
Hi ,


Try out the following formula in A10 :


=IF(AND(A10>MAX(OFFSET(A10,-8,0,8,1)),A10>MAX(OFFSET(A10,1,0,9,1))),A10,"")


Copy it down as far as you need to.


In case the calculations take too long , or other calculations in your workbook are slowed down because of using OFFSET , we might have to replace the OFFSET with INDEX. The resulting formulae might , of course , be longer.


Narayan
 
Hi Narayan,

thanks you are always helpful, I will try this out, but do you think this can be achieved using a semi/non volatile formula which is what I am trying to acheive.


regards

GB
 
You must log in or register to reply here.
Back
Top