LinkBack URL
About LinkBacks
Instead of having to go through each 6 digit code and find the max/min/median, how do I structure an if function within a max/min function to go through it and pull the max by code? Workbook attached. WHen I tried max if it gave me #VALUE! error.
Instead of having to go through each 6 digit code and find the max/min/median, how do I structure an if function within a max/min function to go through it and pull the max by code? Workbook attached. WHen I tried max if it gave me #VALUE! error.
Untested as excel is playing up - Will post example once it works but in the mean time use the following syntax
=MAX(IF(CriteriaRange=Criteria,MaxRange))
Entered with Ctrl Shift Ent
Excel Guru in the making
<----------If the question has been answered to your satisfication please click the Add Repuation star to the left
The NAs make this a bit more complex. In O2,
=MIN(OFFSET($N$1,MATCH(M2,$M$2:$M$256,0),0,COUNTIF($M$2:$M$256,M2)))
drag down.
Should just need to change the MIN to MAX and MEDIAN for the other columns. This assumes your codes are grouped. If you can guarantee no NAs, then The Cman81's solution is cleaner (and could be modified with a check for NA).
Last edited by Pauleyb; 08-22-2013 at 11:39 AM.
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
I don't see where this refers to the data on the first tab, from which I am trying to get the max/min for
max min of list.xlsx
Try if this work for you
Not sure if arrays works on MAC, if not I will find another solution
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
For the max and min you can use pivot table.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi,
See if a Pivot Table answer that shows the Count, Min, Max and Average by ID is what you really want. NO FORMULAS NEEDED. All you have to do is learn a little about Pivots. Note - you also need to make your data a table. You have some merged cells in Row 1 that keeps it from being a table. I've Inserted a blank column (J?) to move the merged column heads off to the right so I could do a pivot on your data. See Sheet2 for my work.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Well, it wasn't clear what you wanted. In your original post, you do not mention where your source data is. In looking at the provided worksheet, the only MIN equations I saw were on the 'Sheet1' sheet and were referencing column N. Looked like you were defining the range by hand and were looking for an easier way to determine the range.Originally Posted by brandnew22
Seriously, when you read through your original post, was it clear what you really wanted?
Pauley
AVERAGE is not a MEDIAN...
Last edited by RobertMika; 08-22-2013 at 04:40 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks