Help with blank cells in range

Excel Help for Help With Blank Cells In Range in Analyzing Corporate Financial Data

Help With Blank Cells In Range

Rate this: (3/5 from 1 vote)

	Hi guys, needing a bit help.  I am using the following formula to return the item that occurs most in a range. =INDEX(B2:B164,MODE(MATCH(B2:B164,B2:B164,0))) This works fine but I have to give the exact range or it returns a 0. My problem is that each week I add more data to the list that is being searched so i need to be able to increase the range to a higher number in preperation for this.  I can manually alter this each week but it would be better if I didn't have to.  Thanks in advance fopr your help.
	Posted by tophs1012 on 02 Apr 2011

Replies - Displaying 1 to 3 of 3

Order Replies By: Most Recent | Chronological | Highest Rated

	OFFSET and COUNTA for dynamic range Rate this: (3/5 from 1 vote) In this case, you can create a dynamic range by using the OFFSET and COUNTA functions. To do so, replace B2:B164 with OFFSET($B$2,0,0,COUNTA($B:$B),1).
	Excel Business Forums Administrator
	Posted by Excel Helper on 02 Apr 2011

offset and counta Rate this: (3/5 from 1 vote) do you mean replace all of the ranges with your suggestion =INDEX(OFFSET($B$2,0,0,COUNTA($B:$B),1),MODE(MATCH(OFFSET($B$2,0,0,COUNTA($B:$B),1),OFFSET($B$2,0,0,COUNTA($B:$B),1),0))) because this gives me an error I would like my formula to work like this to allow me to add more data evry week, but it will not allow the extra cells that are blank. =INDEX(B2:B300,MODE(MATCH(B2:B300,B2:B300,0)))

Posted by tophs1012 on 02 Apr 2011

	INDIRECT with COUNTA Rate this: (4/5 from 2 votes) You're right.  The OFFSET returns the value - not the address reference.  Try instead: INDIRECT("B1:B" & TEXT(COUNTA(B:B),"#"))
	Excel Business Forums Administrator
	Posted by Excel Helper on 02 Apr 2011

Displaying page 1 of 1