View Single Post
  #4  
Old June 23rd, 2004, 08:34 AM
David McRitchie
external usenet poster
 
Posts: n/a
Default Picking out the max

The suggested formula will only work if the numbers
keep going up within a range. And maybe that is the
way your numbers are -- if they are sorted. Try sticking
$99.99 in the middle of prices for a Part Number.

Here is a page with a downloadable addin that might
be of interest. by Dan Herrera
http://www.geocities.com/datamasterf...ionAddIns.html

The nice thing about the looking for answers on the web or in
the newsgroup archives is that you can even make up a
function name and seem if anyone can match a question for
a non existent function. I wasn't really expecting to find that
someone made up a function, but MAXIF was what I tried for
and it was what I found.

My own attempt with array formulas (Ctrl+Shift+Enter)
I couldn't get Column F without having
the formula in Column E.


--A-- ---B----- ---C---- ---D-- ----E---- ----F----
Part price price 0 FALSE
907086 $40.54 99.99 FALSE
907086 $99.99 99.99 99.99 TRUE 99.99
907086 $47.70 47.7 99.99 FALSE
907095 $45.96 54.06 FALSE
907095 $47.31 54.06 FALSE
907095 $54.06 54.06 54.06 TRUE 54.06
907102 $40.54 47.7 FALSE
907102 $41.74 47.7 FALSE
907102 $47.70 47.7 47.7 TRUE 47.7
907111 $56.62 66.61 FALSE
907111 $58.28 66.61 FALSE
907111 $66.61 66.61 66.61 TRUE 66.61

C2: =IF(AND($A2=$A3,$B2$B3),"",$B2)
D2: {=MAX(IF(A$1:A$13=A2,B$1:B$13,FALSE))}

E2: {=B2=MAX(IF(A$1:A$13=A2,B$1:B$13,FALSE))}
F2: =IF(E2,B2,"")
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"foolio " wrote in message ...
Works like a charm Pika, Thx


---
Message posted from http://www.ExcelForum.com/