|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#12
May 5th, 2005, 07:01 PM
|
|||
|
|||
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 
|
|
#13
May 5th, 2005, 07:31 PM
|
|||
|
|||
|
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
|
|
#14
May 5th, 2005, 07:37 PM
|
|||
|
|||
|
Dunno. Try it and report back.
-- Don Guillett SalesAid Software "sonofroy" wrote in message news  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
May 5th, 2005, 07:38 PM
|
|||
|
|||
|
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
|
|
#16
May 5th, 2005, 07:47 PM
|
|||
|
|||
|
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
|
|
#17
May 5th, 2005, 07:48 PM
|
|||
|
|||
|
Your formula removed the zeros thanks.
"Don Guillett" wrote: Dunno. Try it and report back. -- Don Guillett SalesAid Software "sonofroy" wrote in message news 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
|
|
#18
May 5th, 2005, 07:48 PM
|
|||
|
|||
|
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")),"")
|
|
#19
May 5th, 2005, 08:14 PM
|
|||
|
|||
|
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
|
|
#20
May 6th, 2005, 07:36 PM
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| sumproduct for getting counts | Dan | Worksheet Functions | 1 | April 5th, 2004 02:09 AM |
Linear Mode
