| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average percentage excluding zeros spanning numerous tabs
I've got a sheet consolidating the data from various detail sheets... I need
to create a formula for the average percentage excluding zero values... i've got the following formula =AVERAGE(IF(A1:A520,A1:A52)) but that doesn't give the option of using information from different tabs (the data is in the same cell in each tab)... any ideas?! Cheers |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average percentage excluding zeros spanning numerous tabs
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :3"))&"!A1:A52"),"0"))/
SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT(" 1:3"))&"!A1:A52"),"0")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Willabo" wrote in message ... I've got a sheet consolidating the data from various detail sheets... I need to create a formula for the average percentage excluding zero values... i've got the following formula =AVERAGE(IF(A1:A520,A1:A52)) but that doesn't give the option of using information from different tabs (the data is in the same cell in each tab)... any ideas?! Cheers |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average percentage excluding zeros spanning numerous tabs
List your sheet names in a range of cells, for example B1:B5, then try
the following formula.... =SUMPRODUCT(SUMIF(INDIRECT("'"&$B$1:$B$5&"'!A1:A52 "),"0"))/SUMPRODUCT(CO UNTIF(INDIRECT("'"&$B$1:$B$5&"'!A1:A52"),"0")) Hope this helps! In article , Willabo wrote: I've got a sheet consolidating the data from various detail sheets... I need to create a formula for the average percentage excluding zero values... i've got the following formula =AVERAGE(IF(A1:A520,A1:A52)) but that doesn't give the option of using information from different tabs (the data is in the same cell in each tab)... any ideas?! Cheers |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average percentage excluding zeros spanning numerous tabs
Hey Bob,
Been working on this for a while. I'm on an XL97 machine today and it crashed several times when I tried to use SUM in this scenario: With sheet names in Z1 to Z3: This works! =SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z3&"'!A1:A52")," 0"))/SUMPRODUCT(COUNTIF(I NDIRECT("'"&Z1:Z3&"'!A1:A52"),"0")) This crashes! =SUMPRODUCT(SUM(INDIRECT("'"&Z1:Z3&"'!A1:A52")))/SUMPRODUCT(COUNTIF(INDIRECT ("'"&Z1:Z3&"'!A1:A52"),"0")) Care to try it on your version?<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message .. . =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :3"))&"!A1:A52"),"0"))/ SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT(" 1:3"))&"!A1:A52"),"0")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Willabo" wrote in message ... I've got a sheet consolidating the data from various detail sheets... I need to create a formula for the average percentage excluding zero values... i've got the following formula =AVERAGE(IF(A1:A520,A1:A52)) but that doesn't give the option of using information from different tabs (the data is in the same cell in each tab)... any ideas?! Cheers |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average percentage excluding zeros spanning numerous tabs
Just tried the same revision (Sumif to Sum) on your formula, and it *also*
caused my XL97 to crash! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Hey Bob, Been working on this for a while. I'm on an XL97 machine today and it crashed several times when I tried to use SUM in this scenario: With sheet names in Z1 to Z3: This works! =SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z3&"'!A1:A52")," 0"))/SUMPRODUCT(COUNTIF(I NDIRECT("'"&Z1:Z3&"'!A1:A52"),"0")) This crashes! =SUMPRODUCT(SUM(INDIRECT("'"&Z1:Z3&"'!A1:A52")))/SUMPRODUCT(COUNTIF(INDIRECT ("'"&Z1:Z3&"'!A1:A52"),"0")) Care to try it on your version?<bg -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message .. . =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :3"))&"!A1:A52"),"0"))/ SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT(" 1:3"))&"!A1:A52"),"0")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Willabo" wrote in message ... I've got a sheet consolidating the data from various detail sheets... I need to create a formula for the average percentage excluding zero values... i've got the following formula =AVERAGE(IF(A1:A520,A1:A52)) but that doesn't give the option of using information from different tabs (the data is in the same cell in each tab)... any ideas?! Cheers |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average percentage excluding zeros spanning numerous tabs
FWIW,
XL02 returned #N/A error and ... did *not* crash! -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Ragdyer" wrote in message ... Just tried the same revision (Sumif to Sum) on your formula, and it *also* caused my XL97 to crash! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Hey Bob, Been working on this for a while. I'm on an XL97 machine today and it crashed several times when I tried to use SUM in this scenario: With sheet names in Z1 to Z3: This works! =SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z3&"'!A1:A52")," 0"))/SUMPRODUCT(COUNTIF(I NDIRECT("'"&Z1:Z3&"'!A1:A52"),"0")) This crashes! =SUMPRODUCT(SUM(INDIRECT("'"&Z1:Z3&"'!A1:A52")))/SUMPRODUCT(COUNTIF(INDIRECT ("'"&Z1:Z3&"'!A1:A52"),"0")) Care to try it on your version?<bg -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message .. . =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :3"))&"!A1:A52"),"0"))/ SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT(" 1:3"))&"!A1:A52"),"0")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Willabo" wrote in message ... I've got a sheet consolidating the data from various detail sheets... I need to create a formula for the average percentage excluding zero values... i've got the following formula =AVERAGE(IF(A1:A520,A1:A52)) but that doesn't give the option of using information from different tabs (the data is in the same cell in each tab)... any ideas?! Cheers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excluding Zero's from Average (SumIF / CountIF) | Excel Worksheet Functions | |||
| Average Percentage help please | New Users to Excel | |||
| Average, Excluding Zeros, Non-Consecutive Range | Excel Discussion (Misc queries) | |||
| Average non continguous cells, excluding zero's | Excel Worksheet Functions | |||
| EXcluding Zeros from the average in a row | Excel Discussion (Misc queries) | |||
Linear Mode
