| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Averages and Counts from different columns
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 |
|
#2
|
|||
|
|||
|
=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) "sonofroy" wrote in message ... 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 |
|
#4
|
|||
|
|||
|
Okay, so how about
=AVERAGE(LARGE(IF(B1:B20="Front",C1:C20),ROW(INDIR ECT("2:"&COUNTIF(B1:B20,"F ront")-1)))) still an array formula -- HTH RP (remove nothere from the email address if mailing direct) "Don Guillett" wrote in message ... But this average needs to throw out the high and low. -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... =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) "sonofroy" wrote in message ... 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 |
|
#5
|
|||
|
|||
|
seems to work...
-- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... Okay, so how about =AVERAGE(LARGE(IF(B1:B20="Front",C1:C20),ROW(INDIR ECT("2:"&COUNTIF(B1:B20,"F ront")-1)))) still an array formula -- HTH RP (remove nothere from the email address if mailing direct) "Don Guillett" wrote in message ... But this average needs to throw out the high and low. -- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... =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) "sonofroy" wrote in message ... 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 |
|
#6
|
|||
|
|||
|
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")),"") |
|
#7
|
|||
|
|||
|
i get ####
"Bob Phillips" wrote: =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) "sonofroy" wrote in message ... 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 |
|
#8
|
|||
|
|||
|
never mind i am a dummy it is working?
Is there a way to get a count of the number of "Fronts" "Bob Phillips" wrote: =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) "sonofroy" wrote in message ... 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 |
|
#9
|
|||
|
|||
|
=SUMIF(B1:B20,"Front")
-- HTH RP (remove nothere from the email address if mailing direct) "sonofroy" wrote in message ... never mind i am a dummy it is working? Is there a way to get a count of the number of "Fronts" "Bob Phillips" wrote: =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) "sonofroy" wrote in message ... 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 |
|
#10
|
|||
|
|||
|
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 SalesAid Software "sonofroy" wrote in message ... 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 |
|
#11
|
|||
|
|||
|
Thanks in this formula does it throwout any zeros found in "b" column for the
MIN? "Don Guillett" wrote: 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 SalesAid Software "sonofroy" wrote in message ... 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 |
|
#12
|
|||
|
|||
|
Pretty good golfer
-- Don Guillett SalesAid Software "sonofroy" wrote in message ... Thanks in this formula does it throwout any zeros found in "b" column for the MIN? "Don Guillett" wrote: 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 SalesAid Software "sonofroy" wrote in message ... 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 |
|
#13
|
|||
|
|||
|
Those are not my real scores. Did the array formula remove zeros from the MIN
function? "Don Guillett" wrote: Pretty good golfer -- Don Guillett SalesAid Software "sonofroy" wrote in message ... Thanks in this formula does it throwout any zeros found in "b" column for the MIN? "Don Guillett" wrote: 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 SalesAid Software "sonofroy" wrote in message ... 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 |
|
#14
|
|||
|
|||
|
Dunno. Try it and report back.
-- Don Guillett SalesAid Software "sonofroy" wrote in message ... Those are not my real scores. Did the array formula remove zeros from the MIN function? "Don Guillett" wrote: Pretty good golfer -- Don Guillett SalesAid Software "sonofroy" wrote in message ... Thanks in this formula does it throwout any zeros found in "b" column for the MIN? "Don Guillett" wrote: 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 SalesAid Software "sonofroy" wrote in message ... 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 |
|
#15
|
|||
|
|||
|
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" wrote: Pretty good golfer -- Don Guillett SalesAid Software "sonofroy" wrote in message ... Thanks in this formula does it throwout any zeros found in "b" column for the MIN? "Don Guillett" wrote: 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 SalesAid Software "sonofroy" wrote in message ... 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 |
|
#16
|
|||
|
|||
|
happens every time when you don't enter array formulas with CSE
-- Don Guillett SalesAid Software "sonofroy" wrote in message ... 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" wrote: Pretty good golfer -- Don Guillett SalesAid Software "sonofroy" wrote in message ... Thanks in this formula does it throwout any zeros found in "b" column for the MIN? "Don Guillett" wrote: 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 SalesAid Software "sonofroy" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
