/ 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 B.D.V. — short for Black Diamond Vertical — jacket and pants are Black Diamond’s most versatile climbing... Read more
This streamlined, midweight thermal layer has an incredibly speedy moisture wicking ability and dries ultra fast if it gets... Read more
October 21, 2014 – Textile Exchange, a global nonprofit dedicated to sustainability in the apparel and textile industry,... Read more
Climbing as a discipline offers plentiful metaphors for tackling life's obstacles - bravery, courage, climbing to... Read more
In tonight's Friday Night Video, we see Alex Honnold soloing Heaven 5.12d in Yosemite Valley. The route starts 3000ft above the... Read more