/ 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
The Lakpa Rita and Kriti Tech jackets are a pair of shell products from the Sherpa Adventure Gear brand – the... Read more
Since launching their fantastic Reactive lighting technology Petzl have been producing brighter and longer lasting torches that... Read more
Caroline Ciavaldini...Those in the loop of the competition scene a few years back would no doubt have heard this name -... Read more
Urban climber James Kingston will be on stage at all UK screenings to answer questions about his remarkable film... Read more
In British climbing, when we talk about the cutting edge of the sport in the modern day there is one name that will ALWAYS... Read more