Averages and Counts from different columns

[Guest]

Hi I have a golf handicapping spredsheet set up like this
B C
Front 41
Back 30
Front 53
Back 43
Front 37
Back 29

I am trying to get an average for the data in "C" for the "Front" and then
seperately for the "Back"

But this average needs to throw out the high and low.

SUMPRODUCT will not give me a count of Fronts and Backs, and AVERAGE will
not work because I can not get a min function to leave out zeros.

Help Please!
Thanks a bunch

 

[Bob Phillips]

=AVERAGE(IF(B1:B20="Front",C1:C20))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

try this ARRAY formula which must be entered with ctrl+shift+enter. ONE line

=(SUM(IF(A3:A9="Front",B3:B9))-MIN(IF(A3:A9="Front",B3:B9))-MAX(IF(A3:A9="Fr
ont",B3:B9)))/(COUNTIF(A3:A8,"Front")-2)

 

[Don Guillett]

But this average needs to throw out the high and low.

 

[Guest]

i get ####

=AVERAGE(IF(B1:B20="Front",C1:C20))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

never mind i am a dummy it is working?

Is there a way to get a count of the number of "Fronts"

 

[Guest]

Thanks in this formula does it throwout any zeros found in "b" column for the
MIN?

 

[Don Guillett]

Pretty good golfer

 

[Bob Phillips]

=SUMIF(B1:B20,"Front")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Okay, so how about

=AVERAGE(LARGE(IF(B1:B20="Front",C1:C20),ROW(INDIRECT("2:"&COUNTIF(B1:B20,"F
ront")-1))))

still an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Those are not my real scores. Did the array formula remove zeros from the MIN
function?

 

[Guest]

I am also trying to show my High and Low Rounds on the front and back

I am using =MAX(if(a3:a9="Front",B3:b9)) and I get #value!

And I can not figure out a formula for the Low round on the front

Can you help we these also?
Thanks

 

[Don Guillett]

seems to work...

 

[Don Guillett]

Dunno. Try it and report back.

 

[Don Guillett]

happens every time when you don't enter array formulas with CSE

 

[Guest]

I used CSE but it told me formula would not work in merged cells.

 

[Guest]

Your formula removed the zeros thanks.

Dunno. Try it and report back.

 

[Harlan Grove]

Bob Phillips wrote...
....

=AVERAGE(LARGE(IF(B1:B20="Front",C1:C20),
ROW(INDIRECT("2:"&COUNTIF(B1:B20,"Front")-1))))

....

LARGE does to much work. And if there were only 2 or fewer instances of
"Front" in col B, this returns an error.

Alternative,

=IF(COUNTIF(B1:B20,"Front")>2,
TRIMMEAN(IF(B1:B20="Front",C1:C20),2/COUNTIF(B1:B20,"Front")),"")

 

[Don Guillett]

unmerge em.

 

[Guest]

How do I get the Minimum for the rounds on the "Front" without it returning
zero.

I am using the =MIN(IF(a3:a9="Front",B3:B9)) and it returns zero