|
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 |
=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 |
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 |
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 |
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 |
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 |
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 |
=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 |
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 |
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 |
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 |
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 |
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 |
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 |
I used CSE but it told me formula would not work in merged cells.
"Don Guillett" wrote: 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 |
Your formula removed the zeros thanks.
"Don Guillett" wrote: 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 |
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")),"") |
unmerge em.
-- Don Guillett SalesAid Software "sonofroy" wrote in message ... I used CSE but it told me formula would not work in merged cells. "Don Guillett" wrote: 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 |
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 "Don Guillett" wrote: unmerge em. -- Don Guillett SalesAid Software "sonofroy" wrote in message ... I used CSE but it told me formula would not work in merged cells. "Don Guillett" wrote: 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 |
=MIN(IF(--(A3:A9="Front")*(B3:B9<0),B3:B9))
"sonofroy" wrote in message ... 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 "Don Guillett" wrote: unmerge em. -- Don Guillett SalesAid Software "sonofroy" wrote in message ... I used CSE but it told me formula would not work in merged cells. "Don Guillett" wrote: 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 |
Excellent it worked!! Thanks so much for your help!!!
"N Harkawat" wrote: =MIN(IF(--(A3:A9="Front")*(B3:B9<0),B3:B9)) "sonofroy" wrote in message ... 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 "Don Guillett" wrote: unmerge em. -- Don Guillett SalesAid Software "sonofroy" wrote in message ... I used CSE but it told me formula would not work in merged cells. "Don Guillett" wrote: 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 |
| All times are GMT +1. The time now is 03:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com