|
Average IF Question
My data table looks like this:
BOXID 60103 60101 60622 60626 BOX005 0.00% 30.51% 0.00% 0.00% BOX009 0.05% 0.53% 0.01% 0.00% BOX017 16.31% 14.20% 19.71% 23.34% BOX050 0.19% 0.02% 1.92% 0.88% BOX060 0.00% 0.00% 0.00% 0.00% BOX074 0.00% 0.00% 0.00% 0.00% Is it possible for a formula to look at the BOXID and the 2nd and 3rd characters of the first row above and average the values. The result table would look like this (formula would be in B2:D3): 01 02 06 BOX005 15.25% 0.00% 0.00% BOX017 15.25% 0.00% 21.52% Thank you in advance. |
Average IF Question
Hi!
These column headers have to be formatted as TEXT: 01 02 06 This table is in the range A10:E16: BOXID 60103 60101 60622 60626 BOX005 0.00% 30.51% 0.00% 0.00% BOX009 0.05% 0.53% 0.01% 0.00% BOX017 16.31% 14.20% 19.71% 23.34% BOX050 0.19% 0.02% 1.92% 0.88% BOX060 0.00% 0.00% 0.00% 0.00% BOX074 0.00% 0.00% 0.00% 0.00% Formula entered in B2 as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ISERROR(AVERAGE(IF($A$11:$A$16=$A3,IF(MID($B$1 0:$E$10,2,2)=B$2,$B$11:$E$16)))),0,AVERAGE(IF($A$1 1:$A$16=$A3,IF(MID($B$10:$E$10,2,2)=B$2,$B$11:$E$1 6)))) Copy across to D2 then down as needed. Format the cells as PERCENTAGE Biff "carl" wrote in message ... My data table looks like this: BOXID 60103 60101 60622 60626 BOX005 0.00% 30.51% 0.00% 0.00% BOX009 0.05% 0.53% 0.01% 0.00% BOX017 16.31% 14.20% 19.71% 23.34% BOX050 0.19% 0.02% 1.92% 0.88% BOX060 0.00% 0.00% 0.00% 0.00% BOX074 0.00% 0.00% 0.00% 0.00% Is it possible for a formula to look at the BOXID and the 2nd and 3rd characters of the first row above and average the values. The result table would look like this (formula would be in B2:D3): 01 02 06 BOX005 15.25% 0.00% 0.00% BOX017 15.25% 0.00% 21.52% Thank you in advance. |
Average IF Question
I think I have the wrong references. So, try this one:
=IF(ISERROR(AVERAGE(IF($A$11:$A$16=$A2,IF(MID($B$1 0:$E$10,2,2)=B$1,$B$11:$E$16)))),0,AVERAGE(IF($A$1 1:$A$16=$A2,IF(MID($B$10:$E$10,2,2)=B$1,$B$11:$E$1 6)))) I changed A3 to A2 and B2 to B1. Biff "Biff" wrote in message ... Hi! These column headers have to be formatted as TEXT: 01 02 06 This table is in the range A10:E16: BOXID 60103 60101 60622 60626 BOX005 0.00% 30.51% 0.00% 0.00% BOX009 0.05% 0.53% 0.01% 0.00% BOX017 16.31% 14.20% 19.71% 23.34% BOX050 0.19% 0.02% 1.92% 0.88% BOX060 0.00% 0.00% 0.00% 0.00% BOX074 0.00% 0.00% 0.00% 0.00% Formula entered in B2 as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ISERROR(AVERAGE(IF($A$11:$A$16=$A3,IF(MID($B$1 0:$E$10,2,2)=B$2,$B$11:$E$16)))),0,AVERAGE(IF($A$1 1:$A$16=$A3,IF(MID($B$10:$E$10,2,2)=B$2,$B$11:$E$1 6)))) Copy across to D2 then down as needed. Format the cells as PERCENTAGE Biff "carl" wrote in message ... My data table looks like this: BOXID 60103 60101 60622 60626 BOX005 0.00% 30.51% 0.00% 0.00% BOX009 0.05% 0.53% 0.01% 0.00% BOX017 16.31% 14.20% 19.71% 23.34% BOX050 0.19% 0.02% 1.92% 0.88% BOX060 0.00% 0.00% 0.00% 0.00% BOX074 0.00% 0.00% 0.00% 0.00% Is it possible for a formula to look at the BOXID and the 2nd and 3rd characters of the first row above and average the values. The result table would look like this (formula would be in B2:D3): 01 02 06 BOX005 15.25% 0.00% 0.00% BOX017 15.25% 0.00% 21.52% Thank you in advance. |
| All times are GMT +1. The time now is 10:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com