Error in graph
Hi
I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays |
Error in graph
Replace the second "" with NA() in your formula
if(sum(c12:c24)="",NA(),sum(c12:c24)) of course it will throw the #N/A which can look ugly in a formula but you can hide that using conditional formatting and white fonts -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays |
Error in graph
I don't think that Peo saw that first portion.
=sum() is going to return a number (or error) -- it'll never be "". So maybe: To check the sum: =if(sum(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered at least one number =if(count(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered a number in all the cells in c12:c24 =if(count(c12:c24)<13,NA(),sum(c12:c24)) Peo Sjoblom wrote: Replace the second "" with NA() in your formula if(sum(c12:c24)="",NA(),sum(c12:c24)) of course it will throw the #N/A which can look ugly in a formula but you can hide that using conditional formatting and white fonts -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays -- Dave Peterson |
Error in graph
Thanks and God Jul
-- Regards, Peo Sjoblom "Dave Peterson" wrote in message ... I don't think that Peo saw that first portion. =sum() is going to return a number (or error) -- it'll never be "". So maybe: To check the sum: =if(sum(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered at least one number =if(count(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered a number in all the cells in c12:c24 =if(count(c12:c24)<13,NA(),sum(c12:c24)) Peo Sjoblom wrote: Replace the second "" with NA() in your formula if(sum(c12:c24)="",NA(),sum(c12:c24)) of course it will throw the #N/A which can look ugly in a formula but you can hide that using conditional formatting and white fonts -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays -- Dave Peterson |
Error in graph
Hi
Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)), works with the graph. But it creates another problem in my monthly total. The sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell c26 that has formula sum(c1,c10,c25), now since c25 has value #n/a the total for the month shows #n/a and it gives error in my next graph. is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a in cell c25. Thanks "Peo Sjoblom" wrote: Thanks and God Jul -- Regards, Peo Sjoblom "Dave Peterson" wrote in message ... I don't think that Peo saw that first portion. =sum() is going to return a number (or error) -- it'll never be "". So maybe: To check the sum: =if(sum(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered at least one number =if(count(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered a number in all the cells in c12:c24 =if(count(c12:c24)<13,NA(),sum(c12:c24)) Peo Sjoblom wrote: Replace the second "" with NA() in your formula if(sum(c12:c24)="",NA(),sum(c12:c24)) of course it will throw the #N/A which can look ugly in a formula but you can hide that using conditional formatting and white fonts -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays -- Dave Peterson |
Error in graph
Can all the cells C1, C10 and C25 get this error, if yes you can use
=SUMPRODUCT(SUMIF(INDIRECT({"C1","C10","C25"}),"<= 0"&99^99)) which will ignore any error, if no just check for C25 like =IF(ISNA(C25),SUM(C1,C10),SUM(C1,C10,C25)) -- Regards, Peo Sjoblom "ub" wrote in message ... Hi Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)), works with the graph. But it creates another problem in my monthly total. The sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell c26 that has formula sum(c1,c10,c25), now since c25 has value #n/a the total for the month shows #n/a and it gives error in my next graph. is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a in cell c25. Thanks "Peo Sjoblom" wrote: Thanks and God Jul -- Regards, Peo Sjoblom "Dave Peterson" wrote in message ... I don't think that Peo saw that first portion. =sum() is going to return a number (or error) -- it'll never be "". So maybe: To check the sum: =if(sum(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered at least one number =if(count(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered a number in all the cells in c12:c24 =if(count(c12:c24)<13,NA(),sum(c12:c24)) Peo Sjoblom wrote: Replace the second "" with NA() in your formula if(sum(c12:c24)="",NA(),sum(c12:c24)) of course it will throw the #N/A which can look ugly in a formula but you can hide that using conditional formatting and white fonts -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays -- Dave Peterson |
Error in graph
Hi Peo
Thanks for your reply. The error I get is in the cell , where I have formula sum (a25: d25), now since c25 has value #n/a , the cell value in the formula comes to #n/a and the graph that plots value from the cell that has the formula is also effected. regards "Peo Sjoblom" wrote: Can all the cells C1, C10 and C25 get this error, if yes you can use =SUMPRODUCT(SUMIF(INDIRECT({"C1","C10","C25"}),"<= 0"&99^99)) which will ignore any error, if no just check for C25 like =IF(ISNA(C25),SUM(C1,C10),SUM(C1,C10,C25)) -- Regards, Peo Sjoblom "ub" wrote in message ... Hi Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)), works with the graph. But it creates another problem in my monthly total. The sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell c26 that has formula sum(c1,c10,c25), now since c25 has value #n/a the total for the month shows #n/a and it gives error in my next graph. is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a in cell c25. Thanks "Peo Sjoblom" wrote: Thanks and God Jul -- Regards, Peo Sjoblom "Dave Peterson" wrote in message ... I don't think that Peo saw that first portion. =sum() is going to return a number (or error) -- it'll never be "". So maybe: To check the sum: =if(sum(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered at least one number =if(count(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered a number in all the cells in c12:c24 =if(count(c12:c24)<13,NA(),sum(c12:c24)) Peo Sjoblom wrote: Replace the second "" with NA() in your formula if(sum(c12:c24)="",NA(),sum(c12:c24)) of course it will throw the #N/A which can look ugly in a formula but you can hide that using conditional formatting and white fonts -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays -- Dave Peterson |
Error in graph
Use the formula I provided
-- Regards, Peo Sjoblom "ub" wrote in message ... Hi Peo Thanks for your reply. The error I get is in the cell , where I have formula sum (a25: d25), now since c25 has value #n/a , the cell value in the formula comes to #n/a and the graph that plots value from the cell that has the formula is also effected. regards "Peo Sjoblom" wrote: Can all the cells C1, C10 and C25 get this error, if yes you can use =SUMPRODUCT(SUMIF(INDIRECT({"C1","C10","C25"}),"<= 0"&99^99)) which will ignore any error, if no just check for C25 like =IF(ISNA(C25),SUM(C1,C10),SUM(C1,C10,C25)) -- Regards, Peo Sjoblom "ub" wrote in message ... Hi Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)), works with the graph. But it creates another problem in my monthly total. The sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell c26 that has formula sum(c1,c10,c25), now since c25 has value #n/a the total for the month shows #n/a and it gives error in my next graph. is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a in cell c25. Thanks "Peo Sjoblom" wrote: Thanks and God Jul -- Regards, Peo Sjoblom "Dave Peterson" wrote in message ... I don't think that Peo saw that first portion. =sum() is going to return a number (or error) -- it'll never be "". So maybe: To check the sum: =if(sum(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered at least one number =if(count(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered a number in all the cells in c12:c24 =if(count(c12:c24)<13,NA(),sum(c12:c24)) Peo Sjoblom wrote: Replace the second "" with NA() in your formula if(sum(c12:c24)="",NA(),sum(c12:c24)) of course it will throw the #N/A which can look ugly in a formula but you can hide that using conditional formatting and white fonts -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays -- Dave Peterson |
Error in graph
An alternative is to keep your original formulas in the data table for
display or analysis, and create another range for charting which uses a formula like: =IF(D1)="",NA(),D1) Being linked, the chart will update with the data, but the table will not have the error in the display or in downstream calculations. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ub" wrote in message ... Hi Thanks for the reply. The formula if(sum(c12:c24)=0,NA(),sum(c12:c24)), works with the graph. But it creates another problem in my monthly total. The sum(c12:c24) is saved in cell c25, it shows #n/a. But I have another cell c26 that has formula sum(c1,c10,c25), now since c25 has value #n/a the total for the month shows #n/a and it gives error in my next graph. is there any way to sum(c1,c10,c25), to get the sum of c1 & c10 with #n/a in cell c25. Thanks "Peo Sjoblom" wrote: Thanks and God Jul -- Regards, Peo Sjoblom "Dave Peterson" wrote in message ... I don't think that Peo saw that first portion. =sum() is going to return a number (or error) -- it'll never be "". So maybe: To check the sum: =if(sum(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered at least one number =if(count(c12:c24)=0,NA(),sum(c12:c24)) or to check to see if you've entered a number in all the cells in c12:c24 =if(count(c12:c24)<13,NA(),sum(c12:c24)) Peo Sjoblom wrote: Replace the second "" with NA() in your formula if(sum(c12:c24)="",NA(),sum(c12:c24)) of course it will throw the #N/A which can look ugly in a formula but you can hide that using conditional formatting and white fonts -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have graph that takes value from sheet in my workbook. The sheet has some Excell functions that calculate values. Example for the week of Dec 22 a cell has formaula sum(c12:c24) which is 0, because I have not put any values in cell c12:c24. But on my graph for the week of Dec 24 , it plots value 0. I changed the formaula in cell , if(sum(c12:c24)="","",sum(c12:c24)) to get a blank value in cell, so that my graph does not capture the 0 value, but it still keeps on caturing 0 and plots value in my graph. I have changed the setting on Tools-options-Chart-plot empty cells as not plotted, but it does not work. Please advise what to do so that, the cells in my sheet that have a formula but the value is 0 or "" (blank), do not plot as value 0, but leave them as not plotted because no value exist for that cell. Regards Happy Holidays -- Dave Peterson |
All times are GMT +1. The time now is 01:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com