Formula to calculate average if greater than or equal to 30 (1 Viewer)

[jereece]

I have a spreadsheet where workers enter the number of hours they worked that week. So column A lists the workers name and column B lists the hours worked for the week. In cell C1, I need to get the average time worked ONLY for workers who worked 30 hours or more. For example, if I have 30 workers and 25 of them worked 30 hours or more, I need to calculate the average time worked for the 25 workers.

Can someone help me with this forumula?

Thanks,
Jim

 

[niallc]

I suggest sumif(range,>30)/countif(range,">30").
I think this should do it.
regards

 

[niallc]

Tested again and noticed the >= citeria,
so sumif(range,">=30")/countif(range,">=30") should do what you asked
cheers

 

[jereece]

I suggest sumif(range,>30)/countif(range,">30").
I think this should do it.
regards

Ahhhh yes. That will work.

Thanks,
Jim

 

[unmarkedhelicopter]

You could also use an Array formula =Average(if(range>=30,range))
but I'd say the answer given in niallc's post above is the best (and fastest) option

 

[jereece]

You could also use an Array formula =Average(if(range>=30,range))
but I'd say the answer given in niallc's post above is the best (and fastest) option

Using this method, why would I need to enter the range twice?

Jim

 

[unmarkedhelicopter]

Using this method, why would I need to enter the range twice ? Jim

Well you could try entering it once but the array won't work then.
There are two terms here, the first determines if the individual values are greater, or equal to, 30 and the second passes values such that they may be averaged. This is an array formula (comitted with Ctrl-Shft-Enter).
If you wanted to find the average of values 30<=Values<=45 you could also use =Average(if(and(range>=30,range<=45),range))
Arrays are much more flexible solutions though generally slower, so avoid if you can.

 

[jereece]

This is an array formula (comitted with Ctrl-Shft-Enter).

Thanks. I did not know this. It works now.

I appreciate the help.

Jim