Problem is displaying formula
Hi, I am creating a report in which the avg is formulated. But when there is no data entered in the column the solution shows #DIV/0! for ex I wanted to avg c23 to c27 i gave the formula =AVERAGE(C23:C27) when there is no data in c23 to c27 the display shows #DIV/0! I dont want the msg to appear in the screen. Help needed. -- Sathisc ------------------------------------------------------------------------ Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93340 |
Problem is displaying formula
One way,
=IF(SUM(C23:C27)<0,AVERAGE(C23:C27),"") Regards, Alan. "Sathisc" wrote in message ... Hi, I am creating a report in which the avg is formulated. But when there is no data entered in the column the solution shows #DIV/0! for ex I wanted to avg c23 to c27 i gave the formula =AVERAGE(C23:C27) when there is no data in c23 to c27 the display shows #DIV/0! I dont want the msg to appear in the screen. Help needed. -- Sathisc ------------------------------------------------------------------------ Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93340 |
Problem is displaying formula
A very common problem. The usual approach is:
=IF(ISERROR(yourformula),"",yourformula)) so in your case: =IF(ISERROR(AVERAGE(C23:C27)),"",AVERAGE(C23:C27)) -- Gary''s Student - gsnu200851 "Sathisc" wrote: Hi, I am creating a report in which the avg is formulated. But when there is no data entered in the column the solution shows #DIV/0! for ex I wanted to avg c23 to c27 i gave the formula =AVERAGE(C23:C27) when there is no data in c23 to c27 the display shows #DIV/0! I dont want the msg to appear in the screen. Help needed. -- Sathisc ------------------------------------------------------------------------ Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93340 |
Problem is displaying formula
You can look to see if there are any numbers in that range:
=if(count(c23:c27)=0,"No numbers yet",average(c23:c27)) Sathisc wrote: Hi, I am creating a report in which the avg is formulated. But when there is no data entered in the column the solution shows #DIV/0! for ex I wanted to avg c23 to c27 i gave the formula =AVERAGE(C23:C27) when there is no data in c23 to c27 the display shows #DIV/0! I dont want the msg to appear in the screen. Help needed. -- Sathisc ------------------------------------------------------------------------ Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93340 -- Dave Peterson |
Problem is displaying formula
Thanks a lot guys for the reply -- Sathisc ------------------------------------------------------------------------ Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93340 |
Problem is displaying formula
Hi, another possibility is =average(if(c23:c27<0,c23:c27,false)) entered as an array formula with Ctrl+Shift+Enter Sathisc;333930 Wrote: Hi, I am creating a report in which the avg is formulated. But when there is no data entered in the column the solution shows #DIV/0! for ex I wanted to avg c23 to c27 i gave the formula =AVERAGE(C23:C27) when there is no data in c23 to c27 the display shows #DIV/0! I dont want the msg to appear in the screen. Help needed. -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93340 |
All times are GMT +1. The time now is 01:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com