UKC

Ok, one for the Excel users on here, I'm an occasional user so hopefully I'll explain this ok.

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)

In reply to Phil79:
I've not used TREND but you can use " " to describe blank cells in formulae.
For instance:
=IF(A1=" ", TRUE, FALSE)
Will return True if it is blank.

In reply to Phil79: say how

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.

In reply to Phil79: That's a really weird behaviour. I've just confirmed it myself. Any blank cells in the range cause TREND to error, whereas if you plotted a graph and read off the trend it would work fine. I shall investigate!

In reply to Oujmik: Okay, here is a daft but effective solution for you.

=TREND(INDIRECT("A1:A"&COUNT(A1:A20)),INDIRECT("B1:B"&COUNT(B1:B20)),2)

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.

In reply to Oujmik:

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.

In reply to Phil79:

Thanks guys, I'll have another look at this today and see if I can get your suggestions working.

Ta
Phil