Finding Data
 

Not sure if this is possible or not. Excel 2003. No VBA experience. B4:IS4
has daily production output numbers. I am using a formula like
=COUNTIF($B$4:$IS$4,"625")-COUNTIF($B$4:$IS$4,"650") to find the count of
days where production output was between 626 - 650 pieces and goes in
intervals of 25 up to 1600 in D9:D47. B6:IS6 has the % of scrap product (B6
is scrap % for pieces shipped in B4...etc). Is there a way I can average the
scrap % for the output ranges? For example, I have 5 days where the
production output was between 826 - 850 pieces. Can I find the scrap % for
those 5 days automatically?
--
Mike

Finding Data
 

Try this in E9:

=SUMPRODUCT(($B$4:$IS$4625)*($B$4:$IS$4<=650),($B $6:$IS$6)*($B$4:$IS
$4))/ SUMPRODUCT(($B$4:$IS$4625)*($B$4:$IS$4<=650),($B$ 4:$IS$4))

Format the cell as a percentage.

Hope this helps.

Pete

On Oct 12, 10:06*pm, Mike wrote:
Not sure if this is possible or not. *Excel 2003. *No VBA experience. *B4:IS4
has daily production output numbers. *I am using a formula like
=COUNTIF($B$4:$IS$4,"625")-COUNTIF($B$4:$IS$4,"650") to find the count of
days where production output was between 626 - 650 pieces and goes in
intervals of 25 up to 1600 in D9:D47. *B6:IS6 has the % of scrap product (B6
is scrap % for pieces shipped in B4...etc). *Is there a way I can average the
scrap % for the output ranges? *For example, I have 5 days where the
production output was between 826 - 850 pieces. *Can I find the scrap % for
those 5 days automatically?
--
Mike

Finding Data
 

Thanks! Works great. One more question. Not all fields are filled in yet
in B4:IS4 or B6:IS6 so the result is #DIV/0! unless I adjust it daily. Is
there an IF statement or something that can be added somewhere that will make
it more user friendly so I can leave the large range but won't try to pull
the zeros?
--
Mike


"Pete_UK" wrote:

Try this in E9:

=SUMPRODUCT(($B$4:$IS$4625)*($B$4:$IS$4<=650),($B $6:$IS$6)*($B$4:$IS
$4))/ SUMPRODUCT(($B$4:$IS$4625)*($B$4:$IS$4<=650),($B$ 4:$IS$4))

Format the cell as a percentage.

Hope this helps.

Pete

On Oct 12, 10:06 pm, Mike wrote:
Not sure if this is possible or not. Excel 2003. No VBA experience. B4:IS4
has daily production output numbers. I am using a formula like
=COUNTIF($B$4:$IS$4,"625")-COUNTIF($B$4:$IS$4,"650") to find the count of
days where production output was between 626 - 650 pieces and goes in
intervals of 25 up to 1600 in D9:D47. B6:IS6 has the % of scrap product (B6
is scrap % for pieces shipped in B4...etc). Is there a way I can average the
scrap % for the output ranges? For example, I have 5 days where the
production output was between 826 - 850 pieces. Can I find the scrap % for
those 5 days automatically?
--
Mike