Hi Kurt, Try this: =ABS(MATCH(MAX(A1:E1),A1:E1,0)-COUNT(A1:E1))
________________________________________ Thanks & Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com ________________________________________ On Nov 16, 12:39 am, Kurt <heislerk...@gmail.com> wrote: > 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
