Home > Forum Home > Analyzing Corporate Financial Data > help with blank cells in range Share

Help with blank cells in range

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


Forum TopicPost Reply Login

Help With Blank Cells In Range

Rate this:
(3/5 from 1 vote)
ConfusedHi 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 on
 
Replies - Displaying 1 to 3 of 3Order Replies By: Most recent | Chronological | Highest Rated
Oops
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 on
Confused
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 on
Happy
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 on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for help with blank cells in range in the