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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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 |
Thread Tools | |
Display Modes | |
|
|