if is error then "No Data", if not then average range

I believe the formula should be something like:
iferror(average(C1:F1=TRUE,"No Data), average(C1:F1)
0
SK08
2/4/2010 8:36:26 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
462 Views

Similar Articles

[PageSpeed] 47

=IFERROR(AVERAGE(C1:F1),"No Data")


"SK08" wrote:

> I believe the formula should be something like:
> iferror(average(C1:F1=TRUE,"No Data), average(C1:F1)
> .
> 
0
Utf
2/4/2010 9:21:01 PM
Reply:

Similar Artilces:

Avoiding returning a 0 when no data in cell
I have worked out the formula I need to use (with help from someone from this group). However how do I stop it returning a 0 when no data is present in the cell? My formula: =VLOOKUP($G37,Staffing!$C:$AL,36,FALSE) Many thanks Steve =if(vlookup(...)="","",vlookup(...)) You may want to suppress the #n/a error, too: =if(iserror(vlookup(...)),"",if(vlookup(...)="","",vlookup(...))) Steve wrote: > > I have worked out the formula I need to use (with help from someone > from this group). > > However how do I stop it returnin...

Excel Web Query
Finding a strange problem on Excel Web Queries. Up until 3 weeks ago, no problems. Now, users are reporting problems where no data is being returned. I'm using Windows 2000, SP4. Office 2000 SP3. IE 6 SP1. I have these same exact specs as my coworker, but she can't get data back, and I can. We both can run the .asp page and it returns data quickly as expected. We have a mix of Windows versions and Office versions - some return data, some don't. We get the same message indicating that "This web query returned no data. To modify the query, click OK, click th...

MSP_EpmAssignmentByDay
We have quite a lot of projects in Project Server. Should there be data in MSP_EpmAssignmentByDay table on ProjectServer_Reporting db? Now the table is totally empty. How can we get data to that table? Hi Jyrki, what happens if you publish a project? Does that data get to the table? Are there any failed jobs in your queue? You can try to force a rebuild of your Reporting DB: 1. From PWA - Server Settings - Administrative Backup, take backup of ‘Enterprise Custom Fields’. 2. From PWA - Server Settings - Administrative Restore, restore ‘Enterprise Custom Fields’. You ...

offline
Hello, no data are visible in the offline mode. In the online-mode you can see all activities, contacts and so on. No error, no eventlog entry. I tried the netbios name instead of the FQDN in the systemuserbase and delete the registry keys lastsyncdate, sqlservername and userreplicationId. Same problem :( ??? thank you Here is what I found. If you do the following, your first Go Offline synch will show no data even though the synch is apparently successful: 1 Create AD user account with user logon name domain\JohnB 2 Create CRM user - domain logon name = domain\JohnB 3 Cha...

Printing reports with no data
Actually the title of this question is a little misleading, sorry. This should be fairly simple. I run 5 different reports for 2 different shifts based on certain product catagories that were produced the previous day. Sometimes there is data for those reports and sometimes not. Instead of the report opening in preview, I would rather just have a MsgBox pop up that says there's nothing to report. Other than date and shift information, I'm pulling up the product SKU, description of the product, start and end time, qty and so on. please help! Thanks Bill R. -- John 3:16 "Fo...

need to make cell blank if no data in that row
I have a data in columns, I want data to be shown only if there is data in that row if no data then leave it blank (here is what I am asking for) Stock trade Gain/Loss Gain/Loss all trades abc 4.18% 4.18% def 3.77% 7.95% [no trade] N/A 7.95% [no trade] N/A 7.95% i want trade column to appear blank in cell of gain/loss for all trade instead of showing 7.95%. if no trade made If there is no data but the formula is in column, third line shows 7.95%, 4th line shows 7.95%, 5th line 7.95...

Yes/No Data Type
I have a field with a Yes/No data type that uses a field with a check box on my form. How do I designing a query to show all records with check mark or all records without check mark? I've tried Is Null in criteria field and Is Not Null but this does not work for this type field. Any idea on how to do this type query? -- FL On Thu, 21 Jun 2007 16:33:01 -0700, FL wrote: > I have a field with a Yes/No data type that uses a field with a check box on > my form. How do I designing a query to show all records with check mark or > all records without check mark? I've tr...

Money 2005: "Spending by Category" says "No data to display"?
Hi all, On the Home tab under "Spending by Category" I see: 8/30/04 through 9/28/04 No data to display Well, I do have spending during that time frame. I imagine that the "Spending by Category" thingy is looking in the wrong account(s) and needs to be reconfigured. But I can't for the life of me figure out how -- when I right click it there's no "Customize" option or similar. Any ideas on how to fix this? P.S. I'm able to get data if I click the Reports tab and then "Spending by Category". It's only the "Spendi...

Merging Data into Word and Want Checkboxes (Yes/No data type)
I am trying to merge data from my access database into word. I have several items that are checkboxes in access. How do i get those checkboxes to merge into word so that i see them. Right now they just produce numbers if they are checked or not. Thanks I'm not sure you can do with with a straight mail merge. The following is a snippet of code from one of my applications. The app relies on documents that have already been bookmarked with simple bookmarks (for fields longer than 255 characters), checkboxes (for y/n fields), and textboxes for everything else. The merging is d...

Calculated Item overrides "Show Items with no data" checkbox
I've seen this question asked before, but I haven't seen an adequate answer, so I figured I'd try to ask it again to see if there are any fresh ideas on it: Take an example where you've got raw data of orders that contains the following fields Region City Product Total Sales $ You create a pivot table With Region & City as the Row Fields, Product as the Column Field and Total Sales $ as the data. This works fine. Now if you try to add a calculated item, say the difference in sales of Product A and Product B, the whole pivot blows up and you get every city appearing in ...

how to stop subreports shifting horizontally if no data?
I'm a complete newbie here. Hope I can explain the problem clearly enough and sorry if I get some of the terminology wrong. I'm using Report Designer in SSRS 2005. I have two subreports side by side in a list area on my main report, one on the left and one on the right. If there are data in both subreports, they render exactly as I want them to. But, if the first one (on the left) has no data to display, the second one, which should appear to the right, is rendered shifted over to the left where the first one would have been if there were any data. I would prefer that a b...

Query for fields w/ no data (null?)
In the database I am working on, I would like to be able to create a report with a list of all the properties in the database, accompianed by the date of when the property was inspected. Some properties have never been inspected, and therefore do not have an inspection date - or even an entry in tblInspection. Is there a way for me to build a query that displays all of the properties as well as the date they were inspected, even if they were never inspected? tblProperty.PropStreet = is the field for the property, and tblInspection.InspDate = is the field for the inspection date. Thanks! Yo...

reports that produce no data
In sql server report 2005 (SSRS 2005), I will be generating some reports that may not have any data selected at times. This can you can tell me how can you display a message like 'The report is not generated since no records' were selected for this report. Can you tell me how to solve this problem either with sql and/or a sql server report 2005? If not no is selected, what will sql server report display? Would it display anything at all! You can have a default row that says "No data for this month" and use an iif statement to suppress it if there is data in the ...

hiding control textbox when no data
Hi, I am wondering if there is a way to hide the whole textbox control and does not leave big space when it has no data/value in the Access report. Any help would be greatly appreciated. Thank you in advance Define "big space". Do you mean horizontally or vertically? Have you set the text box Can Shrink property to Yes? -- Duane Hookom MS Access MVP "Associates" <Associates@discussions.microsoft.com> wrote in message news:4964B131-5BBE-4249-8F0C-2C00ED0AA012@microsoft.com... > Hi, > > I am wondering if there is a way to hide the ...

keep value if formula has no data
I have a worksheet that automatically updates everyday and works some calculations for me. How can I get the formula sheet to keep the value from the previous day if there is no data today. Because now it is filling in all my cells with zeros. Thanks -- Nicki Taylor Post your formula. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "NTaylor" <...

No data appearing on SOP invoices
Hi there We have a customer that has been using SOP for about a year and printing SOP documents without any problem. About 2 months ago they implemented Collections Management. Last week was the first time they tried to email statements and invoices to their customers. The statements did not even send, and the invoices went to all the customers, only to find out that all the fields were not appearing on the invoice. The design is printed: logo, lines, labels etc, but no data. If we print it again through SOP it works. What could be causing the problem. Regards Robin Hi Robin-- ...

No data return from a sub-query causes an error message
I have a sub-query which counts the number of referrals received [Date Received]. The user then enters the criteria of the time period between [start date] and [end date] and the locality [Town]. There are 9 different localities. When the query is run and there have been no referrals in a particular locality, the table appears as column headings with no data. The query that this links into (as do several other sub-queries) then also returns no data. How can I get the first sub-query to return data for each of the different localities, with 0 if there have been no referrals? If someone ca...

No data event function
Hello. I have many reports and am trying to use a function to display a message when there is no data in the report: "Report has no data." The function seems to work in that if there is data, a report runs with the data. However, if there is no data, the report does not run but "Report has no data" does not appear to the user. I'd appreciate suggestions. Public gfReportHasNoData As Boolean Public Function PrintPreviewReport(pstrReport As String, pfPreview As Boolean, pstrWhere As String) As String 'Comments: Print or preview a report and handle errors 'ps...

report displays no data
I just tried to create an income and spending report in Money 2004. After entering the custom dates, the report comes up but displays none of the data. Does anyone have any idea why the data is not being picked up by the report. Something in the customization is preventing it? A single category? A setting for unreconciled transactions only? An account that wasn't used back then? "Bill Lioio" <Bill Lioio@discussions.microsoft.com> wrote in message news:4740CAAA-508D-4CF8-8F33-802140CBD2E1@microsoft.com... >I just tried to create an income and spending report in...

No data in dynamic spreadsheet
Hi One of our users is using the MS CRM 3.0 web client and terminal services. When this users exports a dynamic excel spreadsheet and enables automatic refresh no data is visible in the spreadsheet. The user is using Office 2003 and is able to view all records in CRM so the permissions are correct. Anyone have an idea as to what could be causing this? Static excel spreadsheets display data. Does the user need to connect to the SQL database in some way? Appreciate your assistance. Thanks Mark When the spreadsheet is opened, check if there is a message at the top asking to unlock the ...

Socket Problem
Hello everybody, I have a strange problem with socket communikation. I'm using MFC CAsyncSocket to devellop a client-server application. The client is a third party program which I cannot change. The client disconnects after sending a telegramm and reconnects for sending the next. Server listens to a port, client connects, server accepts and receives data. So far - so good. However, after a fiew telegramms succesfully send and received, the server gets no more data. The connect-accept works fine but there is no OnReceive after sending data. I've written a dummy client to reproduc...

When no data report states error
When I run a report based on a query and there is no data to report the report states "error" in the body of the report. Is there a way for the report to say "nothing to report"? Or something else other than "error" When people see error they think something is wrong rather than no data or nothing to report. Thanks, Tom Hi Tom, The standard way of handling this is to use code such as this in the report's On No Data event to prevent the report opening at all: Private Sub Report_NoData(Cancel As Integer) MsgBox "There is no data to report"...

By Passing Reports with no data
I have a series of query driven reports that are run from the on click command of a button on a form. These reports are not opened or printed but saved in a folder on a network share. Once the reports have been created an email is send to specific people letting them know that new reports have been created. Below is the code I am using so far. This code works fine other than it creates a blank report if there's no data to be displayed. I am looking for a way to run the reports but ONLY save reports if they actually have data. ==CODE ============================= Private Sub Command38_...

No data in spreadsheet
We have an attendance file set up in Excel. When they went to use it this morning there was no data, just a blank worksheet. This is the second time its happened in the past two months. I have to restore a copy from backup as I have been unable to figure out what the problem is. We are using Excel 2000 and the file is stored on a network server (full rights to the users) Hi I assume you're using ashared workbook. IMHO this seems to be an Excel bug (we have encountered the same issue). I wouldn't use shared files for this reason! -- Regards Frank Kabel Frankfurt, Germany B...

No data event function
Hello. I have many reports and am trying to use a function to display a message when there is no data in the report: "Report has no data." The function seems to work in that if there is data, a report runs with the data. However, if there is no data, the report does not run but "Report has no data" does not appear to the user. I'd appreciate suggestions. Public gfReportHasNoData As Boolean Public Function PrintPreviewReport(pstrReport As String, pfPreview As Boolean, pstrWhere As String) As String 'Comments: Print or preview a report and handle errors 'ps...