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

Similar Articles

[PageSpeed] 49

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:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Excel 2003 extract repeating cells
I have successfully sorted my data to show repeating entries in relation to two specific columns. I can't seem to figure out how to select these repeating entries (without doing it manually, of course) and putting them either into their own column(s) or an entirely different spreadsheet altogether. The goal is to save time in managing THOUSANDS of documents in this manner so that my colleagues can easily pick up repeating entries and take according action. Doing it manually is very time-inefficient. Nit Wit, You don't really describe enough of your layout or what you actually mean...

Tr again: Modifying Calendar's "reading" view in 2003
In Outlook 2003, Calendar allows me to show the event's content in a "Reading" pane at the right or bottom of the calendar. But the layout of the Reading pane wastes a lot of space. Can its layout or content be modified? No, the content can't be modified. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com...

cell looses name after sorting
Can someone help me with the following problem in Excel 2000: in a table I have attached serveral cells with unique cell names, the values in these cells are used in other sheets. the problem is that when I sort the table, the cell names stay in the original rowposition; they are not sorted! while their values are. So Cell names get different values, and other calculations on my other sheets get messed up! How can I make the cell names relative instead of absolute? thankx in advance, Jim --- Message posted from http://www.ExcelForum.com/ "jimfx >" <<jimfx.109zcv@exc...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Z Report shows zeros for an entire shift
How do I correct the problem described above? All receipts are printed correctly, and all transactions seem to be successful. However, the Z report that is run at the end of the shift prints all zeros? Any ideas??? I am having the same problem. All the transactions are successfull and I can see the sale amounts and etc at crstal reports but all the z, zz and x reports print "$0.00" amount. Does anyone know how to solve this problem? "Maakus" wrote: > How do I correct the problem described above? All receipts are printed > correctly, and all transactions seem t...

4.0 Acceleration Software?
Have heard tell of a software "maybe called accelerate" that can be installed on the CRM server, which will increase speed and performance. My clients are complaining of slowness since crm has been installed. They all use Outlook client for desktop, and say there is a definite latency. I've been searching the web, but coming up empty on a software for this. Is it an urban myth or a secret that needs to be be shared? ...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

Office 2004 SP 11.1.0 installation problems
I'm trying to update Office 2004 on my brand new iBook. I installed Office 2004 from the CD without incident. Then I downloaded SP 11.1.1.0 and 11.1.0 (which according to the MS website is to be installed 1st.) When I try to install, I received a message, "An error prevented the update from completing 11002:2,-14" The "Read Me" file indicates several potential explainatins/solutions none of whice are relevant except for uninstalling Office and then reinstalling it. I tried dragging the Office folder to the trash and reinstalled and am having the same problem. I've t...

Read file info using SMO locks database!
Hi! I'm using SMO from C# to restore a database from a backup file. Since I want to re-use the same datafiles I try to read the datafiles in my destination database and call RelocateFile to use these filenames during the restore. Everyting works fine except when I try to read the current datafilenames (see code below). If I skip this code and just hardcode the paths in relocatefile everything works fine. Database db = svr.Databases[destinationDBName]; foreach (FileGroup group in db.FileGroups) foreach (DataFile file in group.Files) Console.WriteLine...

DAO, Access, zero-length strings?
How can you set that you want to allow zero-length strings in a MS Access database with DAO? Also, how can you set that you want default values? Access doesn't seem to allow "DEFAULT" property... ...

Secondary Axis not crossing at zero
I have a chart with a primary and secondary axis. Values for the primary axis range from 0 to 260, values for the secondary axis range from -50% to 50%. I would like to have the zeros on both axis line up, so that the negative percentages on the secondary axis are clearly identifiable. I can't find a way to do it. In the default chart, the x axis crosses the primary y-axis at 0, but the 0 on the secondary y-axis is at approximately the same height as the value 50 on the primary axis. Please help! Astrid Right-click on an axis. Format Axis/ Scale. Alter the min and max values ...

Detect cell colour
I have spreadsheets where 5 cells have general titles and have different background colours. In other sheets, dozens of cells list topics and are also colour coded to show which general title they refer to. I need to tabulate information so that a row would contain three cells: the general title, a topic and data about it. Is there a command that returns the colour of the cell background, so that I could use this to select the correct title to accompany the topic? Hi, Yes there is: FontC = Worksheets("Planning").Cells(<Row>, <Column>).Font.ColorIndex Interi...

CRM 3.0 Locks up Outlook
I have tried to re-install, tried repair nothing works. What is the deal with this software??????? Hi Mark, Do you own alot of contacts in the CRM system? If so, you first time starting the application after the installation will probably take a long time because it synchronize all your contacts to your Outlook. If you don't want the initial sync, you need to disabled the sync by going to your personal settings in CRM or the registry on your laptop. Darren Liu Crowe Chizek http://www.crowecrm.com On Jul 27, 2:36 pm, Mark <M...@discussions.microsoft.com> wrote: > I have tried ...

Check printing in GP 7.0
Dear Guys, I have check payments for vendors, where I'm dealing with LOCAL CURRENCY & FORIGHN CURRENCY. When I paid by check in LOCAL CURRENCY then the amount in words prints perfectly but if i paid by check in FORIGN CURRENCY then the amount in word prints in numeric which i want to print amount in words instead of numbers. Please help me to print checks amount in words for FORIGN CURRENCY as well. Your suggestion would be appricated on above issue thnx Shamin ...

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...

How do I format cells to a specific number of digits?
I am trying to format a column so I can enter 5 digits only. It has between 5 and 7 digit numbers in it right now and I need to delete the remaining ones. It is about 350 numbers, so it would be a lot of work to go into each cell and delete the remaning numbers. Is therea way I can format the whole column to allow 5 digits? The same happened with letters. I had codes in it that consisted of numbers and letters and the client only wants the first two letters to remain. How can it be formatted so only 2 characters are allowed to keep me from having to enter each cell separately? Hi form...

Very Old Version of MSMoney (3.0)
I'm currently using a version of MS Money from 1994 on Windows 98....works perfectly! I have a new computer using MS XP... When I try to run this version of Money I get a "general protection fault in module "msmoney.exe 0059.0504"". Money Standard 2002 came with the system but I can't find it on the recovery disk to re-install. Would rather not re-install complete system if possible. You might need to run the program in compatibility mode. Find the executable for the program and right click, selecting properties. Set the compatibility option for Windows 95 or 9...

How to search a cell for specific information and to pull out that figure found !
Okay , this maybe a tough one....but I hope its solvable , basically I am uploading products to one of the more popular websites , they have various columns , one of which is comment , in this I can type in up to 1000 characters but cannot contain "<" or ">" The comment section I would like to be able to add at the end something like (min=6.75) , this would be my min selling price. There system allows you to download a re-pricing report, it shows only items where you are not the lowest price, but it's a mix of various products all have various min sell...

how do I enter more than one line in the same Excel Cell by using.
I want to have more than one line of text in the same Excel Cell without using the wrap text format option. For example, I want to put an entire address in the Cell by typing in the name of the person, then hit enter, type the steet address, then hit enter, then type in the city, state, and zip. Use alt-enter to put in a line break. "vsimmons" wrote: > I want to have more than one line of text in the same Excel Cell without > using the wrap text format option. For example, I want to put an entire > address in the Cell by typing in the name of the person, then hit en...

Reading archived file
Newbie here. In trying to open a backed-up file (*.pst) from inside Outllok 2003, I get an error message that "file access is denied." I made the stupid backup. How can I get this file to open? Any ideas? >-----Original Message----- >Newbie here. In trying to open a backed-up file (*.pst) >from inside Outllok 2003, I get an error message >that "file access is denied." I made the stupid backup. >How can I get this file to open? Any ideas? >. > You didn't mention what media you saved this backup on but one possible reason that's ha...