/ Excel Question - how to ignore blank cells - help!
I'm using the TREND function to find specific x values from a simple x y data set. So, for example I have two rows of data with potentially up to 20 entries (say A1:B20), but in some cases I might have only 10 entries in each row (say A1:B10) - is there an easy way to get excel to ignore the remaining empty cells (without having to change the array reference every time)?
At the moment it gives me a #VALUE! error, presumbly because some of the cells are empty and the formula wants a number rather than a blank.
The formula is =TREND(A1:A20,B1:B20,x)
I've not used TREND but you can use " " to describe blank cells in formulae.
=IF(A1=" ", TRUE, FALSE)
Will return True if it is blank.
I'm really busy right now but if you used a named range for the argument to the TREND function you could make the named range dynamic, by using a countif function in the range specifier to say how many rows to include.
If you're still looking later I may have time to be more specific this evening.
The range A1:A20 and B1:B20 should cover the largest number of items you will ever need. If you change the columns or the first row, you will need to change the other bits of the formula.
What this is doing is counting how many items there are in each row then constructing the cell reference dynamically to only reference as far as the last non-blank cell. Only works if all your non-blank cells are at the top.
That's the approach I had in mind but coded directly rather than via named ranges. Should work fine.
That technique is really very useful in forms for populating in-cell drop-down lists with variable numbers of data items, as you can put the source data lists on separate sheets and add to them knowing that any new values will then appear in whatever drop-downs you're using the lists in.
Thanks guys, I'll have another look at this today and see if I can get your suggestions working.
Elsewhere on the site
F ounded in 1993, Mountain Hardwear are a pretty young mountaineering clothing and equipment manufacturer but are also one of... Read more
Tonight's Friday Night Video features the Norwegian town of Rjukan, once believed to be the home of the world's tallest... Read more
The release of Peter Jackson's new film The Hobbit: The Battle of the Five Armies on 12th December may not appear to link to... Read more
Rock shoes stink – let’s face it. Boot Bananas are the perfect way to fight the funk and keep them fresh. They help... Read more
Perhaps the perfect Xmas gift for the climber in your life... Wild Country's Crack School has two of the worlds best crack... Read more