Formula for avrgs

[noob]

Column I has sizes of containers ranging from 0.1-88.0, column O has a dollar value of $/Yard. I need a formula that will calculate avrg $/Yrd for all container sizes and mark ones that are below avrg. in a new column.

Thank You

 

[Andrew Poulsom]

You can use a formula like:

=O1<AVERAGE(IF(I$1:I$100=I1,O$1:O$100))

which must be confirmed with Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround the array formula with curly braces {}. The formula can be copied down.

 

[noob]

All I got from that was same avrg that is entered in O2, I need to come up with avrgs. for all size containers and than mark ones below. Not sure if the formula has to be on a separate work sheet to get the avrgs. I was thinking it would have something to do with ( =IF( and THAN formulas plus avrg. formula. but I'm not so sure

 

[Andrew Poulsom]

Sorry my formula got cut off:

=O1 < AVERAGE(IF(I$1:I$100=I1,O$1:O$100))

 

[noob]

OK thank you, so cells marked true are above avrg. false below.. and my second questions is how do I get the avrgs. for each container size? Thank you for the fast reply.

 

[Andrew Poulsom]

The formula returns TRUE for below average, otherwise FALSE.

The second part of the formula returns the average for the container size in column I. You could use a pivot table to create a table of all the averages.

 

[noob]

I've tried the pivot table but it gives me totals vs. avrg. for container size... What would be the formula for the averages

 

[Andrew Poulsom]

Right click the data field and choose Field Settings. In the Summarise by listbox select Average (rather than Sum) and click OK.

 

[noob]

Ok thank you for all you're help