Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
Surprising Zeros in Charts
My model is built. Using #N/A - my chart displays perfectly, zeros suppressed. This formula is what feeds the values in my chart series. =IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),€¯€¯,((AVERAGE(SHEET1!BE2:BM2))*(100/5))) If I alter that error handling to:=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),"#N/A",((AVERAGE(SHEET1!BE2:BM2))*(100/5))) Zero still displays€¦ If I may ask €“ how would I alter this formula to function they way I hope it would? Sincerely, Arturo |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
Try...
=IF(COUNT(Sheet1!BE2:BM2),AVERAGE(Sheet1!BE2:BM2)* 100/5,#N/A) Hope this helps! In article , Arturo wrote: Surprising Zeros in Charts My model is built. Using #N/A - my chart displays perfectly, zeros suppressed. This formula is what feeds the values in my chart series. =IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),€¯€¯,((AVERAGE(SHEET1!BE2:BM2)) *(100/5))) If I alter that error handling to:=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),"#N/A",((AVERAGE(SHEET1!BE 2:BM2))*(100/5))) Zero still displays€¦ If I may ask €“ how would I alter this formula to function they way I hope it would? Sincerely, Arturo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A
It may be treating #N/A as a string not the #N/A value. You may try this one:
=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),NA(),((AVERAGE(SHEET1!BE2:BM2))*(100/5))) Hope this helps, Miguel. "Arturo" wrote: Surprising Zeros in Charts My model is built. Using #N/A - my chart displays perfectly, zeros suppressed. This formula is what feeds the values in my chart series. =IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),€¯€¯,((AVERAGE(SHEET1!BE2:BM2))*(100/5))) If I alter that error handling to:=IF(ISERROR(((AVERAGE(SHEET1!BE2:BM2))*(100/5))),"#N/A",((AVERAGE(SHEET1!BE2:BM2))*(100/5))) Zero still displays€¦ If I may ask €“ how would I alter this formula to function they way I hope it would? Sincerely, Arturo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|