(Urgent) If col D= value, avg col G
 

I have a deadline in two hours if anyone can help with this I would b
extremely grateful.

Here is an attept at psuedocode:
(Column D is sorted so all same values are next to each othe
vertically) this might help

For all col D
If col D = "Assembly"
select range in corresponding row in col G
and average the numbers that are in that range in col G
paste that average in col O row 27

Example:

Col D................ColG

Assembly............4
Assembly............2
Assembly............0
Assembly............3
Check.................1
Check.................6

I need to average the numbers in col G for all Assembly (I also need t
do the max and the stdev calculations for the same ranges.
The spreadsheet will change daily so the range will vary.

Please Help
Thank you very much

Morr

--
Message posted from http://www.ExcelForum.com

(Urgent) If col D= value, avg col G
 

Hi
enter the following formula as array formula (with CTRL+SHIFT+ENTER) in
O27:
=AVERAGE(IF($D$1:$D$100="Assembly",$G$1:$G$100))

similar for MAX and STDEV. e.g.
=MAX(IF($D$1:$D$100="Assembly",$G$1:$G$100))
and
=STDEV(IF($D$1:$D$100="Assembly",$G$1:$G$100))

Note: all are array formulas!

--
Regards
Frank Kabel
Frankfurt, Germany


I have a deadline in two hours if anyone can help with this I would
be
extremely grateful.

Here is an attept at psuedocode:
(Column D is sorted so all same values are next to each other
vertically) this might help

For all col D
If col D = "Assembly"
select range in corresponding row in col G
and average the numbers that are in that range in col G
paste that average in col O row 27

Example:

Col D................ColG

Assembly............4
Assembly............2
Assembly............0
Assembly............3
Check.................1
Check.................6

I need to average the numbers in col G for all Assembly (I also need
to do the max and the stdev calculations for the same ranges.
The spreadsheet will change daily so the range will vary.

Please Help
Thank you very much

Morry


---
Message posted from http://www.ExcelForum.com/

(Urgent) If col D= value, avg col G
 

Thank you for replying quickly.

I tried that formula but it seems like the if statement doesn't wor
because it takes the average of the entire G1:G100 range. Do you kno
how to fix this.

Thank you

Morr

--
Message posted from http://www.ExcelForum.com

(Urgent) If col D= value, avg col G
 

Hi
have you entered the formula with CTRL+SHIFT+ENTER?.

--
Regards
Frank Kabel
Frankfurt, Germany


Thank you for replying quickly.

I tried that formula but it seems like the if statement doesn't work
because it takes the average of the entire G1:G100 range. Do you know
how to fix this.

Thank you

Morry


---
Message posted from http://www.ExcelForum.com/

(Urgent) If col D= value, avg col G
 

Sorry Frank
I forgot to press control/shift/enter.
it works now.

Thanks a lot for the help

Morry


---
Message posted from http://www.ExcelForum.com/