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

Criteria's Max Value in a range

vijay.vizzu

vijay.vizzu

Member
Dear All,


I have searched & studied so many things, but i can't able to derive my formula to get my target. So please help me to get my target.

I have an excel sheet with details like part no. & part name and costing info. It has repeated part nos. so i just used countif function to get unique number without repetition. Assume that i copied all unique part from my main sheet to another sheet and paste it from A1 cell, now i wish to get max value of my A1 cell criteria from my main sheet. i have used VLOOKUP, but it gets first occurance value of my criteria from my main sheet. so i need my criteria max value from a main sheet in a specified range.

I can't able to derive my formula. plz help. i can't upload my file due to web restriction.
 
Array formula:

=MAX(IF(PartNoRange=PartNo,CostRange))


Also, this might have been faster/easier to do with a PivotTable. That would have quickly given you all the unique values, and you can set the data field to show max values. =)
 
Thank you so much Luke M, can u explain the working process of this formula, as per my understanding, in your formula if part no range = part no. how the part range will be equal to part no? and if that true then cost range, how it will be range? and you didn't mention if the condition false then what to do? and you have put in all in max function. Please explain the process, so that in further i can apply this type of ideas in another function.

Thanks in adavance
 
Hi Vijay ,


I am taking the liberty of answering your question because Luke will be able to answer some hours later.


Firstly , the formula is an array formula. If you just type in the formula and press ENTER , you will not get the right result ; you will have to use CTRL SHIFT ENTER to get the right result.


PartNoRange is a range of cells , containing part numbers ; let us assume this range is C10:C18.


CostRange is another range of cells , containing the costs associated with each part number ; let us assume this range is D10:D18.


PartNo is a single cell , say C1 , which contains the part number you wish to match within the range PartNoRange ; if there is a match , the formula will return the maximum cost for that part number ; if there is no match , the formula will return 0.


Thus the formula given by Luke is an easy-to-read version of the following formula :


=MAX(IF(Sheet1!$C$10:$C$18=Sheet1!$C$1,Sheet1!$D$10:$D$18))


Since this is an array formula , what it says is : check whether the part number in cell C1 is present anywhere within the range C10:C18 ; if is present , return the maximum value of the corresponding value in the range D10:D18. So , if the part number is present only once in the range C10:C18 , the formula just returns the corresponding value from the range D10:D18. Thus , if the part number in C1 matches the part number in C17 , the formula will return the value in D17.


Suppose now , there are two or more matches for C1 in the range C10:C18 ; say the matches are in C13 , C14 and C16 ; these form an array of matches. The formula now takes the corresponding values in D13 , D14 and D16 , and then returns the maximum of these three values.


For any IF statement , if the action to be taken when the condition is FALSE is not specified , the IF statement returns the value FALSE , which is equivalent to 0.


Narayan
 
Thank you Narayank, now i understand the formula, but one last question is , when & how we will judge whether we have to use normal formula or array formula.
 
Hi Vijay ,


Normal formulae act on cells , or a range of cells , and return a single result.


Array formulas always act on a set of cells ( these may be physical ranges defined by cell addresses , or they may be virtual ranges defined by the result of criteria e.g. in your example , the result of PartNoRange = PartNo is a virtual range , which consists of those cells which meet the criteria ) ; they may return a single result or an array of results. They may also be entered in a single cell or a range of cells ( always using CTRL SHIFT ENTER ).


Check out this link :


http://www.cpearson.com/excel/ArrayFormulas.aspx


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