Log In
Register
Home
August 25, 2017
Offline
I am creating a line graph with a trend line. The formula is =INDEX(TALLY!$U$9:$U$72,MATCH(N$6,TALLY!$T$9:$T$72,0)).
The formula is copied to several cells. If no data has been entered in the cell then a zero is in the cell, which is what is expected. My question is does the formula has to be modified so that zero is not displayed, so that my trend line is not calculating the zero?
Thanks
August 25, 2017
Offline
Here is the file. Can you help me with the trendline?
Thanks
VIP
April 21, 2015
Offline
no file attached!
VIP
Trusted Members
December 7, 2016
Offline
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
August 25, 2017
Offline
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))
VIP
Trusted Members
December 7, 2016
Offline
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
August 25, 2017
Offline
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.
VIP
April 21, 2015
Offline
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.
VIP
Trusted Members
December 7, 2016
Offline
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
1 Guest(s)
