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
872 Views

Similar Articles

[PageSpeed] 31

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:

MIN formula help
Hello All, I want to know what the Max and Min numbers are using the following formulas. The Max formula seems to work without any problem, however it's the Min that I have a problem with. =Max(A2:A10) =MIN(A2:A10) If I only have numbers in cells A2:A8 and the lowest number is 150, then in that cell that I have the following MIN formula in should show me 150. Instead it's blank because I still have two remaining cells in my range (A9 and A10). How can I fix this. Any and all help is greatly appreciated.. Hi! MIN/MAX ignore empty cells. >Instead it's blank because I ...

Reading system log files
Does anyone know how to read Windows 7 System log files? I need to extract records from System and Application logs and insert them into a list of other events. The insert part is easy - append then sort by date_time. However, I cannot find anything that gives me any help to read the logs. Perhaps the other limitation would be to extract only records dated between set dates and time. -- Steve Hello: The network team in a corporation that I consult for uses VB Script files (filename.vbs) which run under Windows Scripting Host. I'm sure that if you search for sc...

Switching from average cost to FIFO inventory
How would I go about reconciling Inventory and switching from average cost to FIFO method in GP 9? TIA ...

Displaying time from 00:00 to 0.00
I want to do the opposite to what I have seen on the forums and convert time back to decimals ( I work on aspreadsheet but it has been passworded so I can't crack it. :) I want to be able to write (van out) (Van back in)(Total Van time) 7.25 11.25 4.00 Can anyone help me please ( or a way to crack passwords):cool: -- North for Short ------------------------------------------------------------------------ North for Short's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22695 View this thread: http://www.excelforum.com/showthrea...

cell color assistance for excel
How to do formatting of cells automatically by writing Macros when the data is extracted from any tool to excel new user;357439 Wrote: > How to do formatting of cells automatically by writing Macros when the > data > is extracted from any tool to excelI think you need to be a bit more specific about where the data is coming from?, what would be the reason/parameters for colouring the cell(s)? You can always make a good start by using the Macro recorder whilst performing the task! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) --------...

Row select mode to highlight active row of active cell
Yet another simple convenience for folks with large spreadasheets. Whatever row and/or column you are in gets highlighted. Sure, you could select the row or column heading, but so often you are tooling around a large spreadsheet and it would be nice not to have to finger your way over to track across. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestio...

Min bal requirement
My checking account has a minimum balance requirement of $250 to waive the service charge fees, so I want to keep the minimum in the account. Any recommendations on how to balance to the statement? Should I deduct the $250 from the check register & try to remember this every month when I balance to subtract the $250 from the bank's balance? Is there an easier way? Thanks. =?Utf-8?B?S2VsaUI=?= <KeliB@discussions.microsoft.com> wrote on 08 Sep 2007 in group microsoft.public.money: > My checking account has a minimum balance requirement of $250 to > waive the servic...

how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43
how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43 (68 minutes and 43 seconds) In article <5F13B001-9B6B-4D5D-BD81-ACAAF5799CF6@microsoft.com>, "=? Utf-8?B?dmVsZSBQaGFudA==?=" <vele Phant@discussions.microsoft.com> says... > how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43 (68 minutes > and 43 seconds) > =MINUTE(C31)+SECOND(C31)/100 where C31 contains the time in hh:mm:ss. Note that XL may format the cell containing the formula with a time format. If so, change it to 'General'. -- Regards, Tushar Mehta www.tushar-m...

I do not have Publisher. Can I read a pub doc?
A friend has done an A4 prayer sheet in Publisher. He has sent it to me to continue on a monthly basis. I do not have Publisher but to have office 2003 student edition. Can I open the pub doc in any way? Alex Eaden wrote: > A friend has done an A4 prayer sheet in Publisher. He has sent it to > me to continue on a monthly basis. I do not have Publisher but to > have office 2003 student edition. Can I open the pub doc in any way? Another FW who can't be arsed to STFW before posting. http://groups.google.com and that's more help than you deserve. It would have taken you co...

Installation of SP1 (12.1.0) not working
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi everyone, There's a similar post here already, but I couldn't post there for some reason. When I try to install the 12.1.0 Update, it gets stuck at Running AutoUpdate Installer script. Someone mentioned in that other thread that there was message box behind the installer and you had to press OK to continue. I don't have that message box anywhere and I don't know what to do. I am logged in as the admin as well. Thanks for any help! On 7/21/09 4:51 PM, in article 59b788f7.-1@webcrossing.caR9absDaxw,...

combine two cells into new cell with different font format
Hello Everyone, I guess it is an easy one, but I cannot solve it my own. I have two cells for a reference list. Cell A1 contains a name of an author, Cell B1 contains an article title. I want to combine both sells in C1 like A1&B1. The problem is that in Cell C1 the authors name (from A1) should appear normal, while the titel (from B1) should be in italics or bold (both in the same cell) anybody idea how to do that. thanks Kai AFAIK it can't be done. knut "Kux" <kastehux@web.de> skrev i melding news:1152969827.559021.131770@p79g2000cwp.googlegroups.com... > He...

Refering to a cell above another cell
I'm trying to set up a conditional format that depends on the value resulting from taking the value of a cell and subtracting value in the cell above. I assume this would be a conditional format based on a formula? Don't know where to begin. Hi, Not clear what you want to achieve an example will definetely help "rodmiller" wrote: > I'm trying to set up a conditional format that depends on the value resulting > from taking the value of a cell and subtracting value in the cell above. I > assume this would be a conditional format based on a formul...

Average for last 30 days
-- Thanks Ruth I think that to get a useful answer you need to provide a bit more than this:- How do we establish which data relate to the last 30 days? How are your data laid out, which columns? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ruth" wrote: > > -- > Thanks > > Ruth sorry Mike I accidently posted it before i entered in the information I have reposted the question with some detail -- Thanks ...

How to lock cells after a certain days
How can I lock cells B1 to B10 if the date shown in cell A1 maller than today 2 days please help. This little macro will lock cells B1:B10 when the file is opened if the date in A1 is more than 2 days earlier than today's date. This macro must be placed in the workbook module. HTH Otto Private Sub Workbook_Open() With Sheets("TheSheetName") If Date - .Range("A1") > 2 Then .Range("B1:B10").Locked = True End If End With End Sub "Thu Ng" <Thu Ng@discussions.microsoft.com> wrote in mess...

entering 0 to a range of data in a column
How do i Prefix 0 to all the data in a perticular data. Eg. I have different No. like 1234, 3432, 3453 etc in a column and want to see that it shows up as 01234,03432,03453 If it is just for display just use a custom format like 00000, if you need 5 characters in the cell you can use a help column and then use =TEXT(A2,"00000") copy down, edit>paste special as values in place, delete original column -- Regards, Peo Sjoblom "Manoj Nair" <Nair,Manoj@kuwait.army.mil> wrote in message news:90A3390A-3C10-4967-A639-80C0680C7E45@microsoft.com... > How do i Pre...

SDI MAX/MIN childframe?
In an SDI application (NOT MDI), is it possible to HIDE/SHOW the child frame, or max/min the child frame? Thanks in advance On Mon, 18 Jan 2010 18:24:46 -0800 (PST), rockdale <rockdale.green@gmail.com> wrote: >In an SDI application (NOT MDI), is it possible to HIDE/SHOW the child >frame, or max/min the child frame? >Thanks in advance No. It doesn't even make sense to consider this. It *is* possible to change the view (there's even an MSDN article on how to have multiple mutually-exclusive views in SDI) but simply hiding the frame makes no sense. What do ...

Selecting cells in the same row in various columns for ClearingContents
(This is a portion of my macro which loops through multiple sheets, each with various rows of data to test) Sub OpenSheet1() [ Sheet has a Name] 'Opens Sheet 1 for clearing sold out items Worksheets("Sheet1").Activate Range("M2").Activate Dim rwindex As Integer [rwindex is row number] For rwindex = 2 To Range("data") + 1 [ "data" is number of items in the sheet. Row "rwindex", starts at 2) rwindex = rwindex If Cells(rwindex, 13).Value = 0 Then [T...

#DIV/0! error
Dear All I am sure this problem I have can be easily resolved however, I have tried all the online help without success. I want to work out the percentage of two numbers however, to be able include zeros as part of the template. Example C1/B1=D1 as a percentage. Obviously when zero's are entered into the cells it generates the '#DIV/0!'. I have tried entering '=IF(C1=0,"",C1/B1) but it still comes back with the error. Additionally, can you write a conditional format that changes what is displayed when you get an error message? IE. =IF(D1=#DIV/0!,"100"...

8.0 Upgrade
We are going to be performing an upgrade to 8.0 in the next month. Next week we will be doing a test migration and have 3+ weeks scheduled for testing. Does anyone have any documentation of things to consider or issues they had with the upgrade. Anything would be helpful, as I am just trying to ensure all the bases are covered. Thanks, Zach Morgan zachary.morgan@cbs.fiserv.com What are you upgrading from? "Zach Morgan" <ZachMorgan@discussions.microsoft.com> wrote in message news:B4011720-BF9F-4155-AF3C-131AEB8FFD0E@microsoft.com... > We are going to be performing...

Counting down an average
Hi all, I hope someone can help me with my problem. I have a spreadsheet with 3 columns. Column A = Date, Column B = Achieved, Column C = Average per/day to achieve target. If the target for the month is 300 and there is 30 days then the average is 10 per day for the month. if I was to produce on the first day of the month 10 then average for the remaining days for the month is 10. If I was to produce only 5 on the second day of the month then I would like the average to be the total remaining (I.e 300-15) divided by the number of days remaining (I.e 30-2 = 28) to give me the average per day ...

Loacate a range of cells
Excel is Company Forms, that have book numbers in seveval range of cells. VB6 we enter the book number which has a var. and a quanity var. So I want to find the book Number in range B30:B65 and put the quanity in range A30:A65. This will be on Sheet1. Any help would be appreciated. Thanks Don Maybe =vlookup() against a table that contains the book number in column A and quantity in column B (probably on a different sheet???) Donald Johnson wrote: > > Excel is Company Forms, that have book numbers in seveval > range of cells. VB6 we enter the book number which has a > var....

Importing WAB from Outlook Express 6.0 to Outlook 2002
I'm getting a "can not open folder" error when trying to import. The import tool says Outlook Express 4.x, 5; does this mean you can't import from 6.0? Have you tried exporting from OE instead of importing from Outlook? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:968301c3ea9e$7bcf6e70$a001280a@phx.gbl, Scandal wrote: > I'm getting a "can not open folder" error when trying to > import. The import tool says Outlook Ex...

Question about If using Min
My question is regarding using IF statements with MIN or MAX I am takin a class and our spreadsheet deals with a car dealership and sales. I a having problems with creating an IF statement, using MIN, to displa who sold the least cars. I was able to create one for the MAX number o cars sold. Here is the MAX statement which works displaying �Hill� =IF(J11=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Hill",IF(E11=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Hanks",IF(O11=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Sills",IF(E20=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Farmer",IF(J20=M...

Running Average
Hello People, I have a worksheet set up with weather data for May 2005 and it is setup like this: A is the date, B is the high temp, C is the low temp; D is the average temp and E is the normal temp. What I want to do (among other stats) is to keep a running normal temp in E35, which is the data in E1:E5 based on today's date in column A. So on May 2 E35 would have the value of 52.5 (=average(E1:E2) on May 3 E35 would have the value of 52.7 (=avearge(E1:E3) on May 4 E35 would have the value of 52.8 (=average(E1:E4) on May 5 E35 would have the value of 53.0 (=average(...

Transferring cell content between workbooks using cell references
Hi group I want to transfer cell content onto a spreadsheet that has a list of cell references pertaining to a series of other workbooks. The cell references are in the A1 reference style and there are about 800 from 8 different workbooks. Is there an efficient way (macro, formulas?) that you can do this � I can't seem to see the wood for the trees. If anyone has any ideas it would be appreciated. Cheers, Mike Hi not really sure what you want to transfer. Do you want to transfer the values returned from these formulas? -- Regards Frank Kabel Frankfurt, Germany "Kiwi Mike" &l...