Here is the file.  Can you help me with the trendline?

Thanks

no file attached!

Hello,

Yes, if you do not want to have the zeros in the trendline then you have to replace those with #N/A instead.

/Anders

How would I do so?

Would the formula look like  the following?

=INDEX(TALLY!$U$9:$U$72,MATCH(N$6,TALLY!$T$9:$T$72,#N/A))

Hello,

You use the NA() function to get the #N/A error.
You can read more information from Microsoft Office support about the NA function here.

Your formula would then be:
=INDEX(TALLY!$U$9:$U$72,MATCH(N$6,TALLY!$T$9:$T$72,NA()))

Br,
Anders

I have attached the file.  I still need some help.  I would like to be able to graph the data.  The trend line is the issue that I haven't been able to resolve.  The zero's.

I think your problem more has to do with the number of data where the trendline is based on.

It now 'calculates' the zero's as well and so you see the blue downgoing line. This is independed wether the formula is correct or not.

I'm not sure if you can limit the trendline in a way it only calculates the data already given. Hope somebody else here can show that way.

Anders Sehlstedt said
Hello,

You use the NA() function to get the #N/A error.
You can read more information from Microsoft Office support about the NA function here.

Your formula would then be:
=INDEX(TALLY!$U$9:$U$72,MATCH(N$6,TALLY!$T$9:$T$72,NA()))

Br,
Anders  

Hi,

Forget my previous reply, it was wrong. I need to remind myself to always double check before giving a reply. The zero in the end of the MATCH() function just tells that you wants an exact match, so keep it.

You do need to change your data in TALLY worksheet, where you have a zero in columns U and V, either type in #N/A! or use an IF formula to check that if the result is 0 then use NA(), else show the value of the calculation. See attached file.

Br,
Anders

Perfect!  Thank you