min,average>0 if 52 cells read zero

I have 52 cells w265 that all read zero until data is entered this means one 
cell in each worksheet example Week 1 w265, week 2 w265, week 3 w265 to week 
52 w265. on a seperate work sheet im trying to caculate min & average for 
efficiency but when data is entered for min i get zero because of other cells 
that read zero or the average is incorrect too low I have to have zero,s in 
place on the 52 worksheets but i don,t want io include them when calculating 
min or average on seperate work sheet can some please help 
0
Utf
11/15/2009 5:43:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1163 Views

Similar Articles

[PageSpeed] 7

Mike,

The easiest thing to do is to completely empty all your data cells, or have 
formulas that fill the averaged cells with "" until you enter data, and 
Excel will ignore them for numeric calculations.

HTH,
Bernie
MS Excel MVP


"Mike" <Mike@discussions.microsoft.com> wrote in message 
news:9038DB2D-690D-45C8-AF0A-431D1DC73065@microsoft.com...
>I have 52 cells w265 that all read zero until data is entered this means 
>one
> cell in each worksheet example Week 1 w265, week 2 w265, week 3 w265 to 
> week
> 52 w265. on a seperate work sheet im trying to caculate min & average for
> efficiency but when data is entered for min i get zero because of other 
> cells
> that read zero or the average is incorrect too low I have to have zero,s 
> in
> place on the 52 worksheets but i don,t want io include them when 
> calculating
> min or average on seperate work sheet can some please help 

0
Bernie
11/15/2009 6:02:01 PM
=MIN(IF(A1:A100<>0,A1:A100,""))

This is an array formula that must be entered with
CNTRL-SHFT-ENTER
rather than just the ENTER key.


Similar for average.
-- 
Gary''s Student - gsnu200908


"Mike" wrote:

> I have 52 cells w265 that all read zero until data is entered this means one 
> cell in each worksheet example Week 1 w265, week 2 w265, week 3 w265 to week 
> 52 w265. on a seperate work sheet im trying to caculate min & average for 
> efficiency but when data is entered for min i get zero because of other cells 
> that read zero or the average is incorrect too low I have to have zero,s in 
> place on the 52 worksheets but i don,t want io include them when calculating 
> min or average on seperate work sheet can some please help 
0
Utf
11/15/2009 6:31:02 PM
Reply:

Similar Artilces:

Does Microsoft CRM 3.0 extend the Active Directory schema?
Q. Does Microsoft CRM 3.0 extend the Active Directory schema? No, the AD schema is not extended. It simply creates an add'l Organization Unit and some security groups under that OU. No changes that can't be simply undone... "Rifat Yavuz" <Rifat Yavuz@discussions.microsoft.com> wrote in message news:B5E9FBF3-D8B5-4735-AC87-494AA2B81599@microsoft.com... > Q. Does Microsoft CRM 3.0 extend the Active Directory schema? thanks Chris Rifat Yavuz "Chris Resch" wrote: > No, the AD schema is not extended. It simply creates an add'l Organization...

Skip blank cells in diagrams
How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. You can include the function NA() in that field and the zero value for the data won't be displayed. "hlp" <hlp@discussions.microsoft.com> wrote in message news:4FF83D9F-F13E-4815-BDDE-26F44F2E6BE1@microsoft.com... > How do I exclude blank cells in diagrams. If I have an area of data and among > these data some is blank. How do I get excel to not display these data as '0...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...

calculation of cells
Periodically I open a work book and the calculation option has been changed to manual and I cannot figure out why. It seems that it would have to be done by a user and most of my spreadsheets are only used by me. Any ideas out there Mark, Calculation, auto or manual, is set by the first workbook that's opened. It is that way for any other workbooks opened in that instance of excel. Look for a workbook you might have opened first that's been set to Manual and saved that way. Go figure. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------...

returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name down the first colunm and "time in game" across the top and the position they play in array. I then use vlookup for another spreedsheet by "position" down the first column, time across the top and puts the players name into the positions. All this works fine. Since there are 5 more kids than positions, the orginal spreedsheet has blanks when the kids are out of the game. How do I use vlookup or other to extract the 5 sub'd out kids at the bottom of the 2nd spreadsheet? It only returns the nam...

Formatting hyperlinks in an Excel cell 02-16-10
Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses. All of them appear as hyperlinks i.e. blue and underlined but some occasionally seem to lose their hyperlink properties. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on the former does not launch the browser. Is there any way to ensure they are formatted, and work, as hyperlinks please? TIA V ...

Send Email With Cell Conent as Part of the Subject
Hyperlink is able to send email with cell content in the Subject line. My Case =HYPERLINK("mailto:Collections_Admin?subject=Overdue Balance " & "_"& 'Sheet1'!G15,"Email To Collection Agency") Sheet1G15 has the data I want to show in the subject line. Is it possible to do this with Macros? I am using Lotus Notes for email. Thanks Igbert For lotus code see Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "igbe...

eConnect 10.0 and BizTalk 2006R2
I successfully installed BizTalk 2006R2 on W2K3SP2 and downloaded eConnect 10.0 from Customersource (the download is dated 10/17/07 I believe). However despite the documentation stating that there was an adapter for Biztalk, I did not see any option to install the BizTalk adapter when I installed the SDK. Does eConnect 10.0 even recognize BizTalk 2006 R2? ...

I want to format a cell based on an adjacent cells value
I would like a cell automatically formatted in the same way a conditional format works - but the format to be based on the value of an adjacent cell. Any ideas as I cant work out how or if I can achieve this Cumbo, Use Conditional Formatting, but change cell valve is to formula is and use something like this, will change the cell if A1=25 =$A$1=25, so if you select B5 and put this in conditional formatting cell B5 will change when A1 = 25 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on an...

Label a button from a cell content
Hello, Using Excel 97, is their a way to aquire the label for a button from cell on a different worksheet? :confused: Thanks in advance, Sp -- spyrul ----------------------------------------------------------------------- spyrule's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2548 View this thread: http://www.excelforum.com/showthread.php?threadid=38935 ...

inserting texts in cell based on conditions
Hi, I would very much appreciate if someone could help me solving a problem, illustrated by the following example: Column A B C 1 1 "LB" 2 1 "DK" 3 4 1 1 "LB/DK" If there's a 1 in column A, the corresponding cell in column C should get the text "LB" inserted into it. If there's a 1 in column C, the corresponding cell in column C should get the text "DK" inserted into it. If both column A and B have ones in them, the corresponding cell should get the...

How to ignore zero values when plotting a graph
Using Excel 2003. I have a data range for a graph. The values in the cells are the results of a simple If function - If(m28>0,n28,0). The results are taken from a larger data input exercise. But, the graph line (a simple graph!) plots the FALSE value (0) when I would like there to really be no value & hence no plotted point if the result is FALSE. Phil - Change this: If(m28>0,n28,0) to this: If(m28>0,n28,NA()) This results in the ugly #N/A error in the cell, but it makes the chart ignore the point. Debra Dalgleish shows how to hide the ugliness with conditional f...

can I find merged cells?
I'm trying to sort and get the message "merged cells must be the same size". How can I 'find' the merged cells? David, here is a macro by Dave Peterson that will do it Sub Found_Merged_Cells() 'macro looks for merged cells 'By Dave Peterson Dim myCell As Range Dim resp As Long For Each myCell In ActiveSheet.UsedRange.Cells If myCell.MergeCells Then If myCell.Address = myCell.MergeArea(1).Address Then resp = MsgBox(prompt:="found: " _ & myCell.MergeArea.Addre...

Unlinking tracked emails in CRM 3.0
I find that once an email is linked to/tracked by CRM (Customer, Client, Activity) it can't be unlinked! only deleted. For example; A client uses an old CRM generated email containing the tracking token to ask about a new problem. CRM automatically tracks the new email to the old CRM case and I can't untrack it to link it to a new case. Any information would be helpful... Hi, Check out "How to "untrack" Microsoft CRM Outlook e-mails" on following blog http://blog.sonomapartners.com/ -- PLEASE do click on Yes or No button if this post was helpful or not for ou...

Write formula for simple copy and paste to another cell
I have a worksheet that contains hundreds of columns. Each group of three are related to each other (Cols A-C, D-F, etc...). I have this formula that works well for columns A-C =IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=COLUMN($B$1),ROW()=1),IF($A$2=$A$1,"",1),IF(AND(COLUMN()=COLUMN($C$1),ROW()=1),$B$1,IF(AND(COLUMN()=COLUMN($B$2),ROW()=2),IF($A$2=$A$1,2,1),IF(AND(COLUMN()=COLUMN($C$2),ROW()=2),IF($A$2=$A$3,"",IF($A$2=$A$1,$B$2,1)),IF(AND(COLUMN()=COLUMN($B$3),ROW()=3),IF($A...

Place X in cell if criteria met`
Is there a formula to do this? If cell B2 = pencils Put an "X" in cell B7 If cell B2 = pens Put an "X" in cell B8 If cell B2 = erasers Put an "X" in cell B9 Thanks in advance in cells B7 put =if(B2="pencils","x","") in Cell B8 put =if(B2="pens","x","") In cell B9 put =if(B2="erasers","x","") "jhicsupt" wrote: > Is there a formula to do this? > > If cell B2 = pencils > Put an "X" in cell B7 > > If cell B2 = pens ...

Can't read mail in reading pane of outlook 2007 if not in safe mode
Hi all! I have a problem since I installed the outlook 2007 beta 2. I receive mail as nice as possible, but when I want to read it, there is only the title, from whom and to whom data in reading pane. The mesage is, let's say "invisible" :) Anyone have the same prob? When I start outlook 2007 in safe mode, everything works OK. The only prob with safe mode is, that you always have to set the reading pane on and hide the big buttons.... We'll if anyone has a solution...I haven't find it... In the next week I will reinstall the system and see... maybe, just maybe I ...

Extremely Slow to Delete E-mails (and Marks as Read/Unread)
Hi, I recently installed Outlook 2007 on Windows 7 (on my MacBook Pro via Parallels). After installing, I imported the PST file from the version of Outlook on my old computer. Everything worked great at first, but for no obvious reason Outlook has begun really, really dragging (5-10 seconds for actions to be completed) when I attempt to delete e-mails and mark e-mails as read/unread. This is definitely not a computer hardware issue as this is a new and extremely fast computer. This lagtime is becoming unbearable and is resulting in it taking me nearly twice as long to get t...

How to goto cell containing specific date
Thought I asked this before, but can't find the thread w/ my question or any replies... I have a worksheet wih a full year's dates in the cells running down a colum, with other data for each date in the the adjacent columns; Instead of scrolling up & down to a cell with a specific date I'm looking for, is there another way to goto a cell containing a specific date? (e.g., today(), or another specific date) In case this is pertinent: the date series begins with the entry of one date (e.g., 01/01/2010 in cell A1), with the dates in subsequent rows arrived at ...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

CRM 3.0/4.0: Views to show record that is created X Days Ago
I have a simple requirement to create a View with condition that the record is created x days ago, for example the record that is created 3 days ago. The only operator that is available for datetime (e.g. createdon) is only Last X Days, which if i show Last 3 Days, will show the records that were created today, yesterday, and 2 days ago. Is there any operator or any way to show the record that were created 3 days ago? I try to put condition "createdon Last 3 Days" and "NOT createdon Last 2 days", but there is no "NOT" operator in CRM. I try to insert new attribut...

Vista, MS Access 2003 and MS ADO Ext 6.0 vs 2.x
I am running into an issue with MS Access 2003 installed on Vista. Apparantly in the MS ADO Ext 6.0 version either (format, round, or sum) is not in that DLL. However, when I link to v2.8 of the DLL, I don't have an issue. I am getting the following error message when running my query. "Function is not available in expressions in query expressions." Does anyone have other workarounds? I am distributing this application in an MDE format, so I was wondering if the end users may experience issues, if I link to my version of msadox.dll instead of the installed version. I tried s...

Can we use WINCE 6.0 R2 or R3 to build Windows Phone OS Image??
Hi, Can we use WINCE 6.0 R2 or R3 platform builder to build Windows Phone OS Image?? If yes Which option I need to select while building the OS Image?? Since the Windows Phone 7 core is Windoes CE 6.0.I am curious to know whether Windows Phone 7 OS Image can be built using Platform builder. TIA, Nithin On 29 June, 10:29, Nithin <nithin.papd...@gmail.com> wrote: > Hi, > > Can we use WINCE 6.0 R2 or R3 platform builder to build Windows Phone > OS Image?? > > If yes Which option I need to select while building the OS Image?? > > Since the Win...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...