Average formula where blank cells are counted as zeros

I am trying to write an average formula that takes into account the blank 
cells.

I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is 
not treating the blanks as zeros so the answer is much higher than it should 
be.

Should I do a logic formula within the cells?  
0
Utf
4/28/2010 7:44:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
918 Views

Similar Articles

[PageSpeed] 11

If you know the range, you could do the division yourself:

=sum(a1:a6)/6

But this would include other non-numeric cells in the count of cells (6), too.



krwelling wrote:
> 
> I am trying to write an average formula that takes into account the blank
> cells.
> 
> I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is
> not treating the blanks as zeros so the answer is much higher than it should
> be.
> 
> Should I do a logic formula within the cells?

-- 

Dave Peterson
0
Dave
4/28/2010 8:24:05 PM
=AVERAGE(IF($A$2:$A$6<>0,$A$2:$A$6)) AND HIT Ctrl+Shift+Enter to enter the 
array formula.


"krwelling" wrote:

> I am trying to write an average formula that takes into account the blank 
> cells.
> 
> I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is 
> not treating the blanks as zeros so the answer is much higher than it should 
> be.
> 
> Should I do a logic formula within the cells?  
0
Utf
4/28/2010 8:30:01 PM
>=AVERAGE(IF($A$2:$A$6<>0,$A$2:$A$6))

I think the OP wants to include empty cells and evaluate them as numeric 0.

Try one of these this array formulas** .

This one assumes the range contains numbers or empty cells only. No TEXT, no 
formula blanks "".

=AVERAGE(IF(A2:A6,A2:A6,A2:A6))

This one will account for (ignore) TEXT but will evaluate blank/empty cells 
as numeric 0.

=AVERAGE(IF(ISNUMBER(A2:A6),A2:A6,IF(A2:A6="",0)))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"Nadine" <Nadine@discussions.microsoft.com> wrote in message 
news:7A8D82DA-5E0C-492A-BD80-ED08FCCA6148@microsoft.com...
> =AVERAGE(IF($A$2:$A$6<>0,$A$2:$A$6)) AND HIT Ctrl+Shift+Enter to enter the
> array formula.
>
>
> "krwelling" wrote:
>
>> I am trying to write an average formula that takes into account the blank
>> cells.
>>
>> I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it 
>> is
>> not treating the blanks as zeros so the answer is much higher than it 
>> should
>> be.
>>
>> Should I do a logic formula within the cells? 


0
T
4/28/2010 9:46:22 PM
Or, it might be as simple as:

=SUM(A2:A6)/ROWS(A2:A6)

We'd need more details as to what the possible entries are in the range.

-- 
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:eAwr%23wx5KHA.3880@TK2MSFTNGP04.phx.gbl...
> >=AVERAGE(IF($A$2:$A$6<>0,$A$2:$A$6))
>
> I think the OP wants to include empty cells and evaluate them as numeric 
> 0.
>
> Try one of these this array formulas** .
>
> This one assumes the range contains numbers or empty cells only. No TEXT, 
> no formula blanks "".
>
> =AVERAGE(IF(A2:A6,A2:A6,A2:A6))
>
> This one will account for (ignore) TEXT but will evaluate blank/empty 
> cells as numeric 0.
>
> =AVERAGE(IF(ISNUMBER(A2:A6),A2:A6,IF(A2:A6="",0)))
>
> ** array formulas need to be entered using the key combination of 
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
> SHIFT key then hit ENTER.
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Nadine" <Nadine@discussions.microsoft.com> wrote in message 
> news:7A8D82DA-5E0C-492A-BD80-ED08FCCA6148@microsoft.com...
>> =AVERAGE(IF($A$2:$A$6<>0,$A$2:$A$6)) AND HIT Ctrl+Shift+Enter to enter 
>> the
>> array formula.
>>
>>
>> "krwelling" wrote:
>>
>>> I am trying to write an average formula that takes into account the 
>>> blank
>>> cells.
>>>
>>> I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), 
>>> it is
>>> not treating the blanks as zeros so the answer is much higher than it 
>>> should
>>> be.
>>>
>>> Should I do a logic formula within the cells?
>
> 


0
T
4/28/2010 9:56:18 PM
Reply:

Similar Artilces:

Proper formula
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) I have a product list that was typed in uppercase I used the proper formula to change this. I now need to edit the new list but it just gives me the formula not he text in the cell. <br><br>Have tried formatting cells to txt. Any ideas? In article <59bb04bc.-1@webcrossing.JaKIaxP2ac0>, Sands@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (Tiger) > > I have a product list that was typed in uppercase I used the proper formula > to change this. I now need to edit t...

Show which cell has MAX, MIN values?
At the bottom of a couple thousand rows of data, I have =MAX and =MIN formulas. Is there some way I could make the cells beneath my MAX and MIN formulas show me the address of which cell has the displayed MAX or MIN value? At least the row number? Ed Ed, To return the row =MATCH(cell with Max or Min value,range starting in row 1,false) or to return the address, say, in Cell N3000, for a value given in N2999 =ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1)) or to return other matching information, like a name in column A =INDEX(A:A,MATCH(N2999,N1:NN2998,FALSE)) HTH, Bernie MS E...

Counting words............
How can I give the total of how many times a word appears in a column? -- Smokey_Vol ------------------------------------------------------------------------ Smokey_Vol's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29717 View this thread: http://www.excelforum.com/showthread.php?threadid=495182 have a look at =COUNTIF( -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Smokey_Vol" <Smokey_Vol.20eapy_1135177209.4582@excelforum-nospam.com> wrote in message news:Smokey_Vol.20eapy_1135177209.4582@excelforum-nospam.com... > >...

array formula woes
Greetings all, I have found this group of such extreme value over the past 6 months as I take the first steps in learning the wonders of excel... so thank you to all who have offered wisdom. This, however is my first time posting a question so forgive me if I require some feedback on the proper way to ask a question! I am using an array formula which reads a value in one cell then finds the closest match to that value in a range of cells. I use: {=INDEX($AG$5:$AG$53,MATCH(MIN(ABS($AG$5:$AG$53-AA5)),ABS($AG$5:$AG$53-AA5),0))} works great! Next step: I have a formula which reads the resul...

date formulas #5
Please help! I have given an example of part of the spreadsheet i am creating to outline my queery: Report in / Reply due / 1 Sept 2005 / 1 Sept 05 4 Sept 2005 / 18 Sept 05 / 14 Jan 00 / etc, / etc, I have entered the following formula in the 'reply due' column: B1 = A1 + 14 which calculates the date two weeks after the date in the 'report in' column. When i drag the formula down so it applies the entire 'reply due' column the cells aut...

Better formula
Looking for a short cut for this formula. I need to repeat this for ever 5th pair of rows up to row 81. =SUMIF(C10,"he",E10)+SUMIF(C11,"he",E11)+SUMIF(C15,"he",E15)+ ...... Any way to make this quicker formula so I am not making a insanely long formula? One guess ... Try: =SUMPRODUCT((C10:C81="he")*(OR(MOD(ROW(E10:E81),5)={0;1})),E10:E81) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "B.B" <me@mail.com> wrote in message news:Z3T%g.197211$R63.31057@pd7urf1no... > Looking for a short cut for this formula. ...

require cell completion in a form?
I have a very simple form in Excel. I'm not sure if it matters, but the machine I'm on uses Excel 2002, and most of the users are on Excel 2000. The first 2 questions on my form require that the user select from a list of names, and then select their relationship to that person. Then the users answer a series of questions about this person's job performance. I'm using data validation to manage the two drop-down lists - the names and the relationships. However, about a third of my users end up leaving one or both of these cells blank. I have a comment box set up as a remin...

Bad (blank) messages stopping Outlook
We are running Exchange 2003, and clients are accessing via Outlook and POP3. Occasionally, completely empty messages (no sender, no message, no body) will appear in mailboxes, and Outlook will choke. We must then go into OWA, which clearly shows the message to be completely empty, and delete the message before Outlook can receive again. Has anyone encountered this problem before? Is there a fix/work-around? The message Outlook produces at that point is as follows: ---------- Task '(email account name) - Receiving' reported error (0x800CCC0F) : 'The connection to the server was...

Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front. After every week, when new data is entered, I want the formula at the front to use the cell with the new data instead of me having to change the existing formula. For example, my data looks like this: A B C 276 300 421 175 0 0 0 I need a formula that will automatically detect the last number >0 in column C. Thank you for your assistance! =Countif(C2:C1000,">0") so your main page formula might be something like: =...

Averaging only some rows
I have an application where I want to calculate the average of several fields, but only in those rows where the second column is blank. For example: (A3)1 (A4)<blank> (A5)10 (A6)0 (B3)1 (B4)<blank> (B5)20 (B6)2 (C3)50 (C4)some text (C5)500 (C6)0 (D3) 1 (D4)<blank> (D5)15 (D6)4 should give me for a row of averages those calculated for all rows except C - i.e., 1, 15, 2. I appreciate any suggestions on how I can design this Excel 2007 solution. Thanks! Do you mean =AVERAGE(IF(A4:D6="",A3:D5)) as an array formula, commit with Ctrl-Shift-Enter not just Enter ...

Average range of discontinuous cells
I have a block of columns (P-CT) that I need to average but only every 4th one. Example: I need to average P, T, X, AB, AF, AJ, AN, AR, AV, AZ, BD, BH, BL, BP, BT, BX, CB, CF, CJ, CN, CR. If I just type =AVERAGE(selecting these cells) it gives me 40238 when the only one with a value is cell P. These cells contain dates if anything. They are payment dates. I need the average number of dates it took to pay something. Any ideas for Excel 2003? Thanks. Never mind. I forgot to include my beginning date. It works. Just user error. "Nadine" wrote: > ...

create pivot table formula without the GETPIVOTDATA function
Previous to Excel 2003 I could use a pivot table result in a formula and copy that formula as a relative reference. In Excel 2003 a GETPIVOTDATA fromula is created and uses an absolute reference. Is there a way in Excel 2003 to either make it arelative reference (without editing the formula) or revert to the method used in all prior releases? There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html ebergkes wrote: > Previous to Excel 2003 I could use a pivot table resu...

display cell value in msgbox formatted as %
Hi I have been trying to come up with a way to display a cell value in a msgbox so that it formats properly as a percent. I have tried: Productivity = Format(Range("A1").Value, "###,# %") Msgbox Productivity This always gives me a leading 0 (e.g 015%) and I want it to display 15.0%. So I tried this: Productivity = Format(CStr(Range("A1").Value) * 100, "#,###.0") & "%" and it works ok but... I want to use the value of productivity in computations - which I can't formatted as a string ... Am I missing something - or is it as simple...

Can multiple cell results be displayed in a single cell?
Is it possible to display the contents of multiple separate cells in another single cell? If the results of several cells say B1, B2, and B3 are the amounts $10, $15 and $20, I would like to display them in cell A1 as follows: $10 $15 $20 I could do this statically by using text and the 'Alt|Enter' function within the cell. However, these amounts will change from time to time so I'd like a link. Is this possible or not? Thanks for any help. Brett Brett One way: In A1: ="$"&B1&CHAR(10)&"$"&B2&CHAR(10)&"$"&B3&am...

Using color to ignore cells in a formula?????
Is it possible to set up a formula in excel to ingore certain cells if you should change the color of that cell to a color. Example: =sum(a1:a50) if cell A25 was blue how can I ignore it so that it is not calculated? Mike A. Hi only with VBA. See: http://www.cpearson.com/excel/colors.htm and http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards Frank Kabel Frankfurt, Germany "Mike A." <MikeA@discussions.microsoft.com> schrieb im Newsbeitrag news:65C99320-9FE3-4E11-81C2-C262B82B565B@microsoft.com... > Is it possible to set up a formula in excel to ingore cer...

Appending data in cells that utilize a Dropdown list.
Hello all, I would like to append entries in cells that utilize a dropdown list. Currently, when I select (in this case names) from my dropdown list I cannot append them if I want to add more than one to a cell. If I select another name from the dropdown, it erases the first entry. Is there some way to append entries within cells that use this form of validation so that I can enter more than one in a cell from the dd list? -- Thanks, Geek using Office XP Professional Hi Geek Debra Dalgleish has an example workbook showing how to do this http://www.contextures.com/excelfiles.html...

Worksheet Change Event log for multiple cells
Dear All, I have a spreadsheet that is available here: http://www.filefactory.com/file/b02e5h4/n/Worksheet_change_event.zip (although I scanned it for viruses please make sure you do it again prior to opening it as I cannot guarantee it's worm free). I would like to create a log file in an additional sheet (hidden probably) that would record every activity from column E after clicking a button assigned to a cell in that column and show these records in a worksheet Totals in a specific row. For example: Column A from a worksheet „Totals” corresponds with column A in ...

Using Excel error #N/A in a formula
I would like to capture the error message #N/A in an IF statement Would like to use IF(MATCH(C1,Range,0)=#N/A,"OK","STOP") but it does not seem to work. What is the correct syntax to capture the #N/A error message and display "OK" instead of #N/A? =IF(ISNA(MATCH(C1,Range,0)),"OK","STOP") HTH Jason Atlanta, GA >-----Original Message----- >I would like to capture the error message #N/A in an IF statement. > >Would like to use IF(MATCH(C1,Range,0)=#N/A,"OK","STOP") but it does not seem to work. What is the...

Excel: When printing some cells will not print text in them
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) When I print an Excel 08 spread sheet, two of the cells that have text in them will not print the text in the cell. All the other cells with text prints fine. The text does not even show up on print preview in the print dialogue box. Any suggestions on how to get it to include the text in these two cells? ...

#N/A Values : Returned by Formulas vs Entered Manually
Hello; When some cells of the data series have #N/A values returned by formulas, the corresponding chart fails. But if the #N/A values are manually entered into those same cells, the chart works fine !! To my understanding, Excel Charts treat cells with #N/A values as empty cells, so one may select the relevant chart option to "leave gaps", which is perfect. With this apparent different interpretation (by Excel Chart) of the same #N/A values in the data series, how can I make the #N/A values returned by formulas acceptable by the chart ?? Thank you for your help. The #N/A ...

Connecting Cell with Lines
I have seen MSExcel printouts that have dotted or solid lines of different colors connecting different cells in a sheet. Is that something that can be done in MSExcel or would they have been added outside of MSExcel? If it can be done in MSExcel, how? In article <QbYYg.13615$GR.1871@newssvr29.news.prodigy.net>, "jerry" <jerryc314@sbcglobal.net> wrote: >I have seen MSExcel printouts that have dotted or solid lines of different >colors connecting different cells in a sheet. Is that something that can be >done in MSExcel or would they have been added outside of...

Opening a blank form
Hi guys, I created a form to consult data, the record source is a query that gather information from different tables, the problem is when I open the form it shows data already, I want a blank form when it opens, I already tried including DoCmd.GoToRecord , , acNewRecord in the open from event, but I get “You can go to the specified record”, if I go to the query I can’t modify the data or add a new record that is why the acNewRecord is not working. I’ll really appreciate any help Thanks Maracay wrote: >I created a form to consult data, the record source is a query ...

Return value from cell info
Dear expert, If A1 has a figure 39, If A2 has a figure 3, actually it is meant for row 39 and column C. How can I type the function in B1, so that it can capture the info in C39 (column 3 and row 39) Thanks Elton =INDIRECT(ADDRESS(A1,A2,4,1)) -- Regards! Stefi „Elton Law” ezt írta: > Dear expert, > > If A1 has a figure 39, If A2 has a figure 3, actually it is meant for row 39 > and column C. > How can I type the function in B1, so that it can capture the info in C39 > (column 3 and row 39) > Thanks > Elton Thanks. It works .....

Protecting/locking individual cells? + not displaying #N/A on cel
HI, My spreadsheet is using Vookup formulas and I would like to protect the cells with the formulas from others changing them but still alow the oter cells to be free . Such C1 free to place a number that will allow the protected B1 (which has the look up formulae) to have the resulting detail from the look up -locked/protected. ALSO- I have then copied the formulae down to the page and some cells will not have all the time an item in them so now they are showing the #N/A - can this be not displayed? Ta Mike On Sep 10, 8:47=A0am, MikeR-Oz <Mike...@discussions.microsoft.com> w...

What happened to the column number count in the status bar?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I am new to using Office with a mac, and I have a lame question. I am using Word 2008. There used to be options as to what counts and tools you could put at the bottom of the window. Such as, word count, page number, column number etc. I don't see any options to customize the status bar. Am I missing something? It used to be as easy as right clicking (w/PC) and selecting which tool you wanted. How do I do this with my Mac??? Any help is appreciated. -B Yeah, evidently the guy/gal in charge of those features was out...