|
sumproduct
The two formulas work seperately. I want to combine them. The results
returned are the % of cells with numbers between 0 and 100 on each worksheet. I want the % of cells on both worksheet; so I can't just add the two results together :( =SUMPRODUCT(--(B2:F40),--(B2:F4<=100)/COUNT(B2:F4)) =SUMPRODUCT(--([Monday.xls]Monday!B2:F40),--([Monday.xls]Monday!B2:F4<=100)/COUNT([Monday.xls]Monday!B2:F4)) Thanks |
sumproduct
Novice,
Your formula isn't exactly what you asked for. Cells with 0 will be treated as numbers that don't fall in your range. This will reduce the percentage result. The following formula works the same, except across the two sheets. =(SUMPRODUCT((Sheet1!B2:F40)*(Sheet1!B2:F4<=100) ) +SUMPRODUCT((Sheet2!B2:F40)*(Sheet2!B2:F4<=100) ) )/ (COUNT(Sheet1!B2:F4) + COUNT(Sheet2!B2:F4)) -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Novice" wrote in message ... The two formulas work seperately. I want to combine them. The results returned are the % of cells with numbers between 0 and 100 on each worksheet. I want the % of cells on both worksheet; so I can't just add the two results together :( =SUMPRODUCT(--(B2:F40),--(B2:F4<=100)/COUNT(B2:F4)) =SUMPRODUCT(--([Monday.xls]Monday!B2:F40),--([Monday.xls]Monday!B2:F4<=100)/COUNT([Monday.xls]Monday!B2:F4)) Thanks |
sumproduct
It worked. Thanks for pointing out that cells with 0 reduced the percentage.
"Earl Kiosterud" wrote: Novice, Your formula isn't exactly what you asked for. Cells with 0 will be treated as numbers that don't fall in your range. This will reduce the percentage result. The following formula works the same, except across the two sheets. =(SUMPRODUCT((Sheet1!B2:F40)*(Sheet1!B2:F4<=100) ) +SUMPRODUCT((Sheet2!B2:F40)*(Sheet2!B2:F4<=100) ) )/ (COUNT(Sheet1!B2:F4) + COUNT(Sheet2!B2:F4)) -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Novice" wrote in message ... The two formulas work seperately. I want to combine them. The results returned are the % of cells with numbers between 0 and 100 on each worksheet. I want the % of cells on both worksheet; so I can't just add the two results together :( =SUMPRODUCT(--(B2:F40),--(B2:F4<=100)/COUNT(B2:F4)) =SUMPRODUCT(--([Monday.xls]Monday!B2:F40),--([Monday.xls]Monday!B2:F4<=100)/COUNT([Monday.xls]Monday!B2:F4)) Thanks |
| All times are GMT +1. The time now is 05:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com