I'm trying to count the number of columns between two values in a row: the maximum number in an array (e.g., max (A1:E1) and the last cell of the array (e.g., E1).
So if the array is A1:E1, and B1 has the largest value, I need to return 3 because there are 3 columns from B1 to E1 (including E1). The formula I'm using works fine except when a value in the array shares the same value as last cell (E1) in the array. For example, if B1 has the max value (500), and C1 and E1 are both 100, the formula returns 1. This is the formula I'm using: =ABS(MATCH(maxval,A1:E5,0)-MATCH(E5,A1:E5,0)) (maxval refers to the maximum value in the array) Any suggestions about how to adjust this so it can deal with values that match the last cell? -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
