/ 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
Atom Series: Synthetic insulated mid layers AR: All-Round. Significantly warmer and more protective than a fleece hoody, this... Read more
2014 has been a bumper year for climbing publications. Here's a few of the ones that we have either read, or ones that we... Read more
Nikwax’s uncompromising environmental ethos has once again been recognised and rewarded by a trusted authority in... Read more
Hot Aches Productions premiered their latest film Redemption: The James Pearson Story at Kendal Mountain Festival on... Read more
The British climbing scene is very exciting at the moment. It is quite clear that as a sport it is developing at a rapid rate and... Read more