Average using Sumproduct or ....

I have 12 tabs (same worksheet) that I need to average a number in cell b6.  
The issue is sometimes there is a zero in b6 and I do not want to count it in 
the average.  example:  tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab 
5=0, and so on...  What is a good formula?
0
Utf
4/13/2010 3:01:01 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
777 Views

Similar Articles

[PageSpeed] 8

Try

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B6"),"<>0"))
/SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B6"),"<>0
-- 

HTH

Bob

"heater" <heater@discussions.microsoft.com> wrote in message 
news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471@microsoft.com...
>I have 12 tabs (same worksheet) that I need to average a number in cell b6.
> The issue is sometimes there is a zero in b6 and I do not want to count it 
> in
> the average.  example:  tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
> 5=0, and so on...  What is a good formula? 


0
Bob
4/13/2010 3:26:42 PM
Have you tried:

=3DAVERAGE('tab 1:tab 12'!B6)

?

Hope this helps.

Pete

On Apr 13, 4:01=A0pm, heater <hea...@discussions.microsoft.com> wrote:
> I have 12 tabs (same worksheet) that I need to average a number in cell b=
6. =A0
> The issue is sometimes there is a zero in b6 and I do not want to count i=
t in
> the average. =A0example: =A0tab 1, b6=3D145, tab 2=3D276, tab 3=3D0, tab =
4=3D 123, tab
> 5=3D0, and so on... =A0What is a good formula?

0
Pete_UK
4/13/2010 3:29:24 PM
Try this:

=SUM(Sheet1:Sheet12!B6)/SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B6"),">0"))
-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"heater" <heater@discussions.microsoft.com> wrote in message 
news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471@microsoft.com...
I have 12 tabs (same worksheet) that I need to average a number in cell b6.
The issue is sometimes there is a zero in b6 and I do not want to count it 
in
the average.  example:  tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
5=0, and so on...  What is a good formula? 


0
RagDyeR
4/13/2010 3:49:47 PM
See your other post

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&p=1&tid=d733c201-577a-40ed-bb9d-60b9609a8e90

-- 
Biff
Microsoft Excel MVP


"heater" <heater@discussions.microsoft.com> wrote in message 
news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471@microsoft.com...
>I have 12 tabs (same worksheet) that I need to average a number in cell b6.
> The issue is sometimes there is a zero in b6 and I do not want to count it 
> in
> the average.  example:  tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
> 5=0, and so on...  What is a good formula? 


0
T
4/13/2010 11:21:00 PM
Ever given any thought to writing something on Frequency(), similar to what 
Bob did with SumProduct()?

-- 

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:ubyw7$12KHA.1708@TK2MSFTNGP05.phx.gbl...
See your other post

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&p=1&tid=d733c201-577a-40ed-bb9d-60b9609a8e90

-- 
Biff
Microsoft Excel MVP


"heater" <heater@discussions.microsoft.com> wrote in message
news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471@microsoft.com...
>I have 12 tabs (same worksheet) that I need to average a number in cell b6.
> The issue is sometimes there is a zero in b6 and I do not want to count it
> in
> the average.  example:  tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
> 5=0, and so on...  What is a good formula?



0
RagDyeR
4/15/2010 4:10:13 PM
I need a website!

-- 
Biff
Microsoft Excel MVP


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message 
news:uU0MhYL3KHA.4336@TK2MSFTNGP04.phx.gbl...
> Ever given any thought to writing something on Frequency(), similar to 
> what
> Bob did with SumProduct()?
>
> -- 
>
> Regards,
>
> RD
> -----------------------------------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> -----------------------------------------------------------------------------------------------
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:ubyw7$12KHA.1708@TK2MSFTNGP05.phx.gbl...
> See your other post
>
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&p=1&tid=d733c201-577a-40ed-bb9d-60b9609a8e90
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "heater" <heater@discussions.microsoft.com> wrote in message
> news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471@microsoft.com...
>>I have 12 tabs (same worksheet) that I need to average a number in cell 
>>b6.
>> The issue is sometimes there is a zero in b6 and I do not want to count 
>> it
>> in
>> the average.  example:  tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, 
>> tab
>> 5=0, and so on...  What is a good formula?
>
>
> 


0
T
4/15/2010 11:04:07 PM
True, but maybe something like:

http://www.oaltd.co.uk/MVP/Default.htm
-- 

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:uCm20$O3KHA.1016@TK2MSFTNGP02.phx.gbl...
I need a website!

-- 
Biff
Microsoft Excel MVP


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:uU0MhYL3KHA.4336@TK2MSFTNGP04.phx.gbl...
> Ever given any thought to writing something on Frequency(), similar to
> what
> Bob did with SumProduct()?
>
> -- 
>
> Regards,
>
> RD
> -----------------------------------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> -----------------------------------------------------------------------------------------------
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:ubyw7$12KHA.1708@TK2MSFTNGP05.phx.gbl...
> See your other post
>
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&p=1&tid=d733c201-577a-40ed-bb9d-60b9609a8e90
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "heater" <heater@discussions.microsoft.com> wrote in message
> news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471@microsoft.com...
>>I have 12 tabs (same worksheet) that I need to average a number in cell
>>b6.
>> The issue is sometimes there is a zero in b6 and I do not want to count
>> it
>> in
>> the average.  example:  tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123,
>> tab
>> 5=0, and so on...  What is a good formula?
>
>
>



0
RagDyeR
4/16/2010 2:13:16 AM
Reply:

Similar Artilces:

Need a list of who is using Outlook and OExpress?
Hi, Does anyone know if there is a way to find out who is using Outlook and who is using Outlook Express? I need to generate a list of who still needs to be migrated across to Outlook and Im not sure how to get this list without going to the individual machines and looking. Any help on this matter would be greatly appreciated! kendall You could try running an automated process to check the modified date of a user's Inbox.DBX file. If it's very recent, most likely they're still using Outlook Express. Alternately you could check to see what their default Internet mail progra...

Using From field in outlook 2007 with mail merge
I am desperately trying to send out emails via mail merge and be able to customize my outlook "From" field before sending. However, I never get a chance to make any edits via outlook before the messages are sent from the Word mail merge. Please advise. "GYJ64" <GYJ64@discussions.microsoft.com> wrote in message news:6038977C-A5A4-4CAE-81B8-E2C58A9A079A@microsoft.com... >I am desperately trying to send out emails via mail merge and be able to > customize my outlook "From" field before sending. However, I never get a > chance to make ...

Campaign Mail Merge using More Addresses
Our client wants to store multiple addresses for contacts using More Addresses to define Mail to, Ship to locations. They want to be able to select these other addresses in a campaign using the mail merge function. Is there anyway to select the address by address type when sending a mail merge? thanks, ...

Using OutputDebugString too much can cause slowness in GUI response
FYI, I have been having problems with a ListView with about 100 items, and when I try to scroll vertically or horizontally by dragging the scroll bar, it doesn't catch up immediately, but it looks highlighted and frozen, and catches up later. It turned out the reason for it was that I was using OutputDebugString too much, printing the text for each item and sumitem(There are 8 columns in this list view), and DebugView was open to view the output. When I close DebugView, the problem is gone. I was using OutputDebugString at a rate of 1000/Second. The items also appeared ...

What can I use an upgrade for?
I have recently bought and installed Office Ultimate 2007 onto a new lap top. I now want Microsoft Project 2007 and Visio 2007. Do I need "upgrade" versions of these (I do not have earlier versions) or full versions? many thanks Jean You have to have previous versions of Project and Visio to get the upgrade price. The Office suites do not supply an upgrade path to Visio or Project. You'll have to bite the bullet and buy the full program. "Jean C" <Jean C@discussions.microsoft.com> wrote in message news:1353044F-B57F-4A7B-8AEB-F79D79EF3573@microsoft.com....

Using the contents of a cell in a cell reference
In order to save time it would be useful to create a cell reference that contained a variable. Does anyone know if this can be done and if so what the notation might be? The idea is this: If I normally want to reference a data set from D5:D21 but sometime the length of the data changes an it might be D5:D35 it would be helpful if I could enter 21 or 35 in cell A1 and then reference the data set as D5:D(A1) that way anytime I change the value in A1 my data set would be updated. Thanks in advance Tom I think you want to use =indirect(). A simple example: =SUM(INDIRECT("D5:D&...

How do I use Access 97 on a PC which has Access 2003, windows XP
I have a library of books catalogued o n Access 97 on a software prog using 97. I now have a new PC with XP and Access 2003 and cannot access programme or dat e though it is saved on PC. What do you mean that by "cannot access programme". How are you trying to use it? Just for the record, I have both Access 97 and Access 2003 installed on my Windows XP machine, and have no issues using both, even concurrently. A "problem" is that the file association for .MDB (and .MDE) files will be reset each time you use Access so that the most recently used version is what wil...

How to find Latest n files from a directory using VC++
Dear Experts , I am developing an application in VC++ 6.0 . My application is releated with reading and writing large numbers of file . i have one directory say [C:\Test] which contains 10,000 files and may be more . i want to know how can i find latest 20 files that is created in C:\Test . I am using CFileFind and CFileStatus to find file and to Get Creation time of file but i think as my content of directory increases it will comsume more time for searching latest 20 files ...... Can anyone tell me how to come around this searching problem... is there any readymade class available th...

IMAP Settings and Questions on Use
I'm using O2003 and just changed to using IMAP and was wondering if someone could help out with these questions. 1. Is it possible to change the location of the PST file that IMAP uses. There doesn't seems to be an option. I still have my current PST file with all my data (contacts, tasks, etc) as I want to keep that local. 2. When I purge, it seems I can only purge one folder at a time. There is no option to purge multiple folders at once. Is that correct? 3. There is no auto purge such as purge all items when I close Outlook 2003. Correct? 4. Is there an option to move a deleted ...

Using cell content as reference in formula
I am a beginner with Excel, and a little lost. When creating a formula, is it possible to use the content of a cell, without creating labels, to refer to the cell, or group of cells in which the content appears? For example, in countless places in my spreadsheet, the text "Total" appears in a cell, refering to a numeric value, 4 columns to the right in the same row. I would like to create a formula that adds all the values, wherever they appear 4 columns to the right of the cell containing the text "Total". To take this one step further, I would like to create a secon...

how to automate specifying the account to use when sending mail?
I am using MS Outlook 2003 with my company email as default account. I am also using my gmail/hotmail and other accocunts to both send and receive. With this, Is the following possible: Everytime I write to my boss/colleagues, Outlook should use AUTOMATICALLY use the default mail account to send; which it DOES. But everytime i write to a friend, it should automatically use gmail/hotmail account to send that mail. why do i have to manually choose everytime the account from which to send? it feels stupid. is there a way out? you need to change the account - there isn't a way to te...

MFC application using properysheet and property page do not pop up when navigating between propertypages
Hi, I created a modeless propertysheet, i have added propertypages, when the application is launched the dialog appears on the screen, when i select any page and launch a browser so that the my application is hidden behind the launched browser, now wheen i click on the application in task bar tha dialog is not shown on the dialog.. can anyone help me in this regard. ...

Using an existing chart, how do I update the data
...

Need some advice on using rpc over https in the office
Hi all, I need your advice on these. Is it better to use rpc over https for outlook users inside office than using TCP/IP? Any security concern since we need to allow internal users to access the DMZ server directly - exchange front end sever? If we configure the outlook users to use rpc over https inside the office, what's connection status shown as TCP/IP or https? Thank you. Depends on your Outlook config, if you have set your profile to use TCP\IP on fast connections (LAN) it will use TCP\IP, otherwise it will default to RPC\HTTP such as when at home. Some people create two p...

SYmbol for money market used in investment cash account
My Fidelity Investments account has a cash account that uses a money market fund for the cash. When I downlaod from Fidelity now, I get a new input for the money market fund which would be in additon to the cash portion and not correct. In Money 2002 (my version) the cash account portion is not associated with a fund and its corresponding symbol. Is there anyway I can clear this up? It seems that I need to assign a symbol to the cash portion of my investment account, but I can't see how to do it. In microsoft.public.money, Alan Holzhausen wrote: >My Fidelity Investments a...

Using data val. drop-down list to select offset
I'm using MS Excel '02 w/SP3 to make a spreadsheet that automatically fills in certain fields based on a model number that is selected from a drop-down list created using the data validation method. What I would like to do is have a cell that reflects the index value of the item selected from the drop-down list. That index value is then used in an OFFSET formula that fills in the fields. As an example of what I want to do: I pick the second item in the drop down list; this stores the number 2 in cell "C1"; the formula =OFFSET($A$1,$C$1,1) is entered into "D1&quo...

usual methods don't reset sheet's "used range"
Hi, I'm hoping someone can help me reset excel's perception of the 'use range' I'm using b/c none of the usual tricks have worked (tho the have in the past). It's pretty much the identical problem to the on posted by R Avery in May, and I've tried all of the suggestions offere to him. But since he never wrote back to say what if anything worked I'm having to post this problem again (see his original post at http://www.excelforum.com/showthread.php?s=&threadid=223255&goto=nextnewest). To explain, my dataset runs from A1 to CB603 but the scroll bar exten...

How to Get Sum of the Average in Pivot Table?
Help! I have a Pivot Table that calculated the inventory in average. However, on the total row, I'd like to get the sum of the average. How to do that? Thank you. You already got an answer to your earlier post "You can remove the grand total from the table options and replace it with a =SUM(Range) AFAIK there is no way of having grand total using sum while the rest is average" -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Evanya" <Evanya@discussions.microsoft.com> wrote...

Shorten sumproduct formula
Hello, Is there a way to use a sumproduct formula looking at non-contiguous cells? In cell a1, I have this formula: =SUMPRODUCT((G14<G15)*(H14>G15)*(LEN(G14)>0))+SUMPRODUCT((G16<G15)*(H16>G15) *(LEN(G16)>0)) I tried the following with no success: SUMPRODUCT(((G14,G16)<G15)*((H14,H16)>G15)*(LEN((G14,G16))>0), but gives me a #VALUE Any suggestions? Thanks Andr� Andr�, Not sure why you need to shorten it, but is this any good (not the way you were thinking) =((G14<G15)*(H14>G15)*(LEN(G14)>0))+((G16<G15)*(H16>G15)*(LEN(G16)>0)) -- HTH ...

Should I be using "IF"
I have one column that I enter weekly numbers in (i.e. Column "E", Rows 1 thru 52). I have another column (i.e. "H" also using Rows 1 thru 52) that I want to have excel total all the numbers up, to give me a "Year to Date" total entered so far. If I've entered values up to week 8 (i.e Column "E" I have numbers entered in Rows 1 thru 8), I want the YTD column to show me the total (i.e. Column "H" Row 8 will show me that total of Column "E" Rows 1 thru 8). I can do that easily. What I'm having trouble with is that I want all row...

in excel useing comments how do you add clip art to comments?
in excel useing comments how do you add clip art to comments? Visit Debra Dalgleish's site: http://contextures.com/xlcomments02.html#Picture dhouse wrote: > > in excel useing comments how do you add clip art to comments? -- Dave Peterson Hi, For more information on comments, refer to Debra Dalgliesh's site: http://www.contextures.on.ca/xlcomments03.html#plan Challa Prabhu "dhouse" wrote: > in excel useing comments how do you add clip art to comments? ...

Using COUNTIF
I have a range of number between 1 and 5. Four of the cells in the range display formulas. The formulas I am using to determine the number of 1's, 2's, 3's, etc are: COUNTIF(A1:A233,"1");..... COUNTIF(A1:A233,"5"). How do I get the COUNTIF formulas to count the cells containing the formulas? =SUMPRODUCT(--(A1:A233>=1),--(A1:A233<=5)) or =SUM(COUNTIF(A1:A233,{1,2,3,4,5})) "Jack" wrote: > I have a range of number between 1 and 5. Four of the cells in the range > display formulas. The formulas I am using to determine the number o...

Question: How to use time as an axis label
Greetings, One thing that has bothered me for a while is my inability to use times as an axis label. Typically I have to convert everything to seconds or something whether for use on the X- or Y-axis. I want to know - there must be a way - how I can have a column or row of times in the format hh:mm:ss and use those directly as label in a chart. Any help would be greatly appreciated! Mike I am using Excel 2003, and to use Time, or Date as the X (Category) axis works fine, so long as time is entered as 4:20:35 etc What error do you get? -- Zerex71 Wrote: > Greetings, > > On...

How to use nested IF statements
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi everyone, <br> was wondering if anyone could help me figure out using nested IF statements in excel 2008.I'm used to using it in windows XP and the procedure is pretty straight forward: to nest a statement you just click on the IF button in the &quot;name bar&quot; when you are in the IF False box.....that doesn't seem to work here....any suggestions would be appreciated,thanks. Dunno what "clicking on IF" does, but just type away -- making sure you know whether you want ...

Help-how to install a macro for everyone to use
This may not be in the right category but I thought I would try here. I have a macro that I want to send to 120 people. These people are located in many areas of the country and DO NOT use Excel very often. Originally with 2003, we created an addin that had a custom toolbar with a button assigned to a macro. They would double click the addin and then when they load their CSV file, the custom button would format it appropriately. This addin would also work with 2007, copy addin to c:\ double click and somehow it created a custom menu with the button on it and it worked. NO...