If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Line chart zero values
I am trying to do a automated line chart that updates as
users input over the next 12 weeks... the chart is consisting of 12 weeks on the x axis and % on the y. The problem im having is the values in the table that the chart is picking up from are formulas... but if the formula result is zero or ""(blank) I want the line chart to ignore it... rather than plot zero values making the line drop when a nil value or blank... for rest of the whole 12 weeks...? eg week 1 = 10% week 2 = 11% week 3 = I want the line to stop at week 2...? but because to work out the % is a formula the line is carrying on ? example of the formula is =if(a1="","",a1/100) Can anyone help me on this please ??? |
#2
|
|||
|
|||
Line chart zero values
Hi,
Try using NA() instead of "" Walshy wrote: I am trying to do a automated line chart that updates as users input over the next 12 weeks... the chart is consisting of 12 weeks on the x axis and % on the y. The problem im having is the values in the table that the chart is picking up from are formulas... but if the formula result is zero or ""(blank) I want the line chart to ignore it... rather than plot zero values making the line drop when a nil value or blank... for rest of the whole 12 weeks...? eg week 1 = 10% week 2 = 11% week 3 = I want the line to stop at week 2...? but because to work out the % is a formula the line is carrying on ? example of the formula is =if(a1="","",a1/100) Can anyone help me on this please ??? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
|
|||
|
|||
Line chart zero values
Thanks you loads for looking, I have tried :
=if(a1="",n/a(),"a1/100) but this returns #name for the answer and line carries on ??? also tried putting : =if(a1="","n/a()","a1/100) but displays n/a() and chart line still carries on ??? Am I doing this right ? -----Original Message----- Hi, Try using NA() instead of "" Walshy wrote: I am trying to do a automated line chart that updates as users input over the next 12 weeks... the chart is consisting of 12 weeks on the x axis and % on the y. The problem im having is the values in the table that the chart is picking up from are formulas... but if the formula result is zero or ""(blank) I want the line chart to ignore it... rather than plot zero values making the line drop when a nil value or blank... for rest of the whole 12 weeks...? eg week 1 = 10% week 2 = 11% week 3 = I want the line to stop at week 2...? but because to work out the % is a formula the line is carrying on ? example of the formula is =if(a1="","",a1/100) Can anyone help me on this please ??? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info . |
#4
|
|||
|
|||
Line chart zero values
Its ok, I played about with n/a and have sorted it !!!
Thank you very much !!! ended up : =if(a1="",#n/a,"a1/100) - this worked !!! WEAH !!! -----Original Message----- Thanks you loads for looking, I have tried : =if(a1="",n/a(),"a1/100) but this returns #name for the answer and line carries on ??? also tried putting : =if(a1="","n/a()","a1/100) but displays n/a() and chart line still carries on ??? Am I doing this right ? -----Original Message----- Hi, Try using NA() instead of "" Walshy wrote: I am trying to do a automated line chart that updates as users input over the next 12 weeks... the chart is consisting of 12 weeks on the x axis and % on the y. The problem im having is the values in the table that the chart is picking up from are formulas... but if the formula result is zero or ""(blank) I want the line chart to ignore it... rather than plot zero values making the line drop when a nil value or blank... for rest of the whole 12 weeks...? eg week 1 = 10% week 2 = 11% week 3 = I want the line to stop at week 2...? but because to work out the % is a formula the line is carrying on ? example of the formula is =if(a1="","",a1/100) Can anyone help me on this please ??? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info . . |
Thread Tools | |
Display Modes | |
|
|