Results 1 to 8 of 8

Formula with ROWS Function appears not to return correct number of rows

Threaded View

  1. #1
    Registered User
    Join Date
    02-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    4

    Formula with ROWS Function appears not to return correct number of rows

    I am trying to get right a complex formula in excel 2013 and the issue seems to be that the ROWS function at the end of the function fails to count the number of rows because the $ and the absence of the $ are not working, and the Rows formula is always returning the 1st row of the array.

    The complete expression is

    =INDEX(input, SMALL(IF((INDEX(input, ,$O$103, 1)<=$O$101)*(INDEX(input, ,$O$103, 1)>=$O$102), ROW(input)-MIN(ROW(input))+1, ""), ROWS(A$106:A106)),, 1)

    Input is an array referenced by the above formula which is entered as an array formula further down the sheet. The objective is to return rows which match specific criteria defined as constants in the sheet ($O$103=1 representing column 1 of the array, $O$101 and $O$102 are alphabetic and define the criteria to return the row (e.g. rows with contents between AB and CD). The problem is that the ROWS function ALWAYS returns 1 (being the number of rows. The expression does not change further down the sheet i.e. ROWS(a106:a106).

    The above expression is entered as an array formula

    I have uploaded a spreadsheet created in excel 2013 with this problem

    Any help would be more than appreciated

    richard
    Attached Files Attached Files
    Last edited by rzw30; 02-21-2013 at 10:05 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1