Using TREND function without zeros in a range

[Rok1]

Hello all,

I would need some help with an analysis I am doing.
I have a column A with values y and column B with values x for my graph. Some cells are empty or have a zero in it.

eg.
1.2 3.2
1.3 3.3
0 0
4.4 10.9
[empty] [empty]
1.0 3.0

I want to calculate trends on this data - but excluding all zero values or empty cells from the range.
Bearing in mind, as more values become available, the trend value will change - which is why I need it to calculate the trend based on cells in the range that contain a value.

Can anyone offer any advice or assistance as to how this can be done?

Thanks & Cheers
Rok

[etaf]

replace the 0 with #N/A - they are not then plotted on the graph

[Rok1]

Thank you for your answer.

I tried replacing zeros (and empty cells) with #N/A but I still get an #VALUES! error.
My code is
=TREND(Sheet2!A3:A60;Sheet2!B3:B60;Sheet1!B6;TRUE)
where I call cells A3:B60 for y and x values. B6 is the new x.

I also tried
=TREND(IF(Sheet2!$A3:$A60 <>0;Sheet2!$A3:$A60);IF(Sheet2!$B3:$B60 <>0;Sheet2!B$3:$B60);B6;TRUE)
with same result.

[etaf]

sorry, i dont have a solution

[amphinomos]

#VALUE! - Occurs if any of the values in the supplied known_y's, [known_x's] or [new_x's] arrays are non-numeric.

[Rok1]

Thank you for your answer.

All values are numbers or empty cells. I also changed cells category from "general" to "number".
Any other ideas?

[amphinomos]

Sorry no more ideas on my part

[MrShorty]

In my experience with the TREND() it just does not like empty, error, or other non-numeric values, as amphinomos indicated. 0,0 data points are "allowed" but they will incorrectly skew the trendline toward the origin. The obvious solution, in my mind, is to "remove" the offending data points from the known_x and known_y arguments.

One possible solution I see is to extract the "usable" subset from the main data set into another part of the spreadsheet. Your TREND() function will then use the ranges containing the extracted data as its arguments. I believe this can be accomplished with filters, but, again, this isn't a function I use, so I'm not very good at structuring them. Because the size of the extracted data set will vary as real data is added to the main list, you may want to consider using dynamic named ranges as the arguments, so they will update as needed.

Another possible solution (really it's the same solution, just using visual basic instead of spreadsheet only functions) is to create a UDF. This would look something like this skeleton pseudocode

Please Login or Register  to view this content.

As I noted, they are really the same solution, in both cases, we are creating a subset of the main data set to use in the TREND() function. It's more a question of which approach you will be more comfortable with and/or able to learn more easily. If you already have experience with VBA (or other programming language), then the UDF approach might be easier to build. If not, then it might be easier to learn Excel's built in filtering utilities and use those.

[Rok1]

It works, thanks

If it will help anyone in the future, here is what I did:
=TREND(OFFSET(Sheet2!$A$3; 0; 0; COUNTA($A3:$A500); 1); OFFSET(Sheet2!$B$3; 0; 0; COUNTA($B3:$B500); 1);C18;TRUE)