|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 11:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com