Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average using Sumproduct or ....
Try
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:12"))&"'!B6"),"<0")) /SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT( "1:12"))&"'!B6"),"<0 -- HTH Bob "heater" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average using Sumproduct or ....
Have you tried:
=AVERAGE('tab 1:tab 12'!B6) ? Hope this helps. Pete On Apr 13, 4:01*pm, heater wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average using Sumproduct or ....
See your other post
http://www.microsoft.com/communities...d-60b9609a8e90 -- Biff Microsoft Excel MVP "heater" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average using Sumproduct | Excel Worksheet Functions | |||
Sumproduct...Average | Excel Discussion (Misc queries) | |||
AVERAGE & SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct Average | Excel Discussion (Misc queries) | |||
sumproduct & average | Excel Worksheet Functions |