Need help with two Bowling Formulas

[Tom Rogers]

I have setup a spreadsheet to keep track of my personal bowling scores. I
have made everything automatic except 2 formulas that I cannot figure out,
can someone help?

1) Series Total column. I have a row of 3 numbers that get a simple SUM
formula in the 4th column of that row. These series totals range in a column
from G4 - G32. I have conditionally formatted them so that if the formula
result is zero, paint the cell white so I don't see the zeros. So in this
formula column, I only get an actual formula result when I actually have
scores input in the rows. I want to find the MIN series total in the entire
column, but the MIN formula is giving me a zero (because I have hidden
zeros). How can I get the minimum actual number, not being a zero.

2) I need to figure high series WITH handicap added in. Say G8 has high
series number, I need to multiply J7 by 3 then add this to the high series
number in G8. How do I do this? I need the cell number for the high series
returned, so that I can point the J column cell # to one less than the G
column cell #.

TIA,

-Tom

[swatsp0p]

First, you can use the SMALL function to find the 2nd smallest score. e.g.:

=SMALL(G4:G32,2)

However, rather than using cond. formatting to 'hide' a zero, use an IF statement to not return anything if D, E and F are blank, e.g.

=IF(SUM(D4:F4)=0,"",SUM(D4:F4))

Now the MIN function will work, as your cells will either contain the SUM of your scores or be blank.

Second:

in a blank column (H?) enter this formula and copy down e.g. H4:H32

=IF(G4=MAX($G$4:$G$32),($J$7*3)+G4,"")

This will find the cell in G with the max value, and add your single game handicap which has be trebled. OR-- You could just do the math in K7 (=J7*3) and use that in your formula:

=IF(G4=MAX($G$4:$G$32,$K$7+G4,"")

Good Luck

[Roger Govier]

Hi Tom

I think the following should work
=SUMIF(G4:G32,">0")

=MAX(G4:G32)+J7*3

Regards

Roger Govier



Tom Rogers wrote:

>I have setup a spreadsheet to keep track of my personal bowling scores. I
>have made everything automatic except 2 formulas that I cannot figure out,
>can someone help?
>
>1) Series Total column. I have a row of 3 numbers that get a simple SUM
>formula in the 4th column of that row. These series totals range in a column
>from G4 - G32. I have conditionally formatted them so that if the formula
>result is zero, paint the cell white so I don't see the zeros. So in this
>formula column, I only get an actual formula result when I actually have
>scores input in the rows. I want to find the MIN series total in the entire
>column, but the MIN formula is giving me a zero (because I have hidden
>zeros). How can I get the minimum actual number, not being a zero.
>
>2) I need to figure high series WITH handicap added in. Say G8 has high
>series number, I need to multiply J7 by 3 then add this to the high series
>number in G8. How do I do this? I need the cell number for the high series
>returned, so that I can point the J column cell # to one less than the G
>column cell #.
>
>TIA,
>
>-Tom
>
>
>
>
>

[Tom Rogers]

I figured out the first one, but the second formula does not work as you
stated. I need the cell reference returned from =MAX(G4:G32), say it is G7.
Then somehow I need to be able to reference J6 * 3 plus the value in G7.

The J6 will change depending on the cell reference returned from
=MAX(G4:G32). If the cell reference returned is G15, then the J cell
reference has to be J14.

Thanx,

-Tom


"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom
>
> I think the following should work
> =SUMIF(G4:G32,">0")
>
> =MAX(G4:G32)+J7*3
>
> Regards
>
> Roger Govier
>
>
>
> Tom Rogers wrote:
>
>>I have setup a spreadsheet to keep track of my personal bowling scores. I
>>have made everything automatic except 2 formulas that I cannot figure out,
>>can someone help?
>>
>>1) Series Total column. I have a row of 3 numbers that get a simple SUM
>>formula in the 4th column of that row. These series totals range in a
>>column from G4 - G32. I have conditionally formatted them so that if the
>>formula result is zero, paint the cell white so I don't see the zeros. So
>>in this formula column, I only get an actual formula result when I
>>actually have scores input in the rows. I want to find the MIN series
>>total in the entire column, but the MIN formula is giving me a zero
>>(because I have hidden zeros). How can I get the minimum actual number,
>>not being a zero.
>>
>>2) I need to figure high series WITH handicap added in. Say G8 has high
>>series number, I need to multiply J7 by 3 then add this to the high series
>>number in G8. How do I do this? I need the cell number for the high series
>>returned, so that I can point the J column cell # to one less than the G
>>column cell #.
>>
>>TIA,
>>
>>-Tom
>>
>>
>>
>>

[Roger Govier]

Hi Tom

Try
=CELL("address",INDEX(G4:G32,MATCH(MAX(G$:G32),G$:G32,0)))

This will return the location of the max value in range G4:G32
You haven't said how the add on changes with location, so unless there
is a formula for this, I can't add to it any more.

Regards

Roger Govier



Tom Rogers wrote:

>I figured out the first one, but the second formula does not work as you
>stated. I need the cell reference returned from =MAX(G4:G32), say it is G7.
>Then somehow I need to be able to reference J6 * 3 plus the value in G7.
>
>The J6 will change depending on the cell reference returned from
>=MAX(G4:G32). If the cell reference returned is G15, then the J cell
>reference has to be J14.
>
>Thanx,
>
>-Tom
>
>
>"Roger Govier" <[email protected]> wrote in message
>news:[email protected]...
>
>
>>Hi Tom
>>
>>I think the following should work
>>=SUMIF(G4:G32,">0")
>>
>>=MAX(G4:G32)+J7*3
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>
>>Tom Rogers wrote:
>>
>>
>>
>>>I have setup a spreadsheet to keep track of my personal bowling scores. I
>>>have made everything automatic except 2 formulas that I cannot figure out,
>>>can someone help?
>>>
>>>1) Series Total column. I have a row of 3 numbers that get a simple SUM
>>>formula in the 4th column of that row. These series totals range in a
>>>column from G4 - G32. I have conditionally formatted them so that if the
>>>formula result is zero, paint the cell white so I don't see the zeros. So
>>>in this formula column, I only get an actual formula result when I
>>>actually have scores input in the rows. I want to find the MIN series
>>>total in the entire column, but the MIN formula is giving me a zero
>>>(because I have hidden zeros). How can I get the minimum actual number,
>>>not being a zero.
>>>
>>>2) I need to figure high series WITH handicap added in. Say G8 has high
>>>series number, I need to multiply J7 by 3 then add this to the high series
>>>number in G8. How do I do this? I need the cell number for the high series
>>>returned, so that I can point the J column cell # to one less than the G
>>>column cell #.
>>>
>>>TIA,
>>>
>>>-Tom
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>

[Tom Rogers]

That did it! Thanx!

-Tom


"swatsp0p" <[email protected]> wrote in
message news:[email protected]...
>
> First, you can use the SMALL function to find the 2nd smallest score.
> e.g.:
>
> =SMALL(G4:G32,2)
>
> However, rather than using cond. formatting to 'hide' a zero, use an IF
> statement to not return anything if D, E and F are blank, e.g.
>
> =IF(SUM(D4:F4)=0,"",SUM(D4:F4))
>
> Now the MIN function will work, as your cells will either contain the
> SUM of your scores or be blank.
>
> Second:
>
> in a blank column (H?) enter this formula and copy down e.g. H4:H32
>
> =IF(G4=MAX($G$4:$G$32),($J$7*3)+G4,"")
>
> This will find the cell in G with the max value, and add your single
> game handicap which has be trebled. OR-- You could just do the math in
> K7 (=J7*3) and use that in your formula:
>
> =IF(G4=MAX($G$4:$G$32,$K$7+G4,"")
>
> Good Luck
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile:
> http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=474176
>

[Tom Rogers]

Thanx, got it working!

-Tom

"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom
>
> Try
> =CELL("address",INDEX(G4:G32,MATCH(MAX(G$:G32),G$:G32,0)))
>
> This will return the location of the max value in range G4:G32
> You haven't said how the add on changes with location, so unless there is
> a formula for this, I can't add to it any more.
>
> Regards
>
> Roger Govier
>
>
>
> Tom Rogers wrote:
>
>>I figured out the first one, but the second formula does not work as you
>>stated. I need the cell reference returned from =MAX(G4:G32), say it is
>>G7. Then somehow I need to be able to reference J6 * 3 plus the value in
>>G7.
>>
>>The J6 will change depending on the cell reference returned from
>>=MAX(G4:G32). If the cell reference returned is G15, then the J cell
>>reference has to be J14.
>>
>>Thanx,
>>
>>-Tom
>>
>>
>>"Roger Govier" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>Hi Tom
>>>
>>>I think the following should work
>>>=SUMIF(G4:G32,">0")
>>>
>>>=MAX(G4:G32)+J7*3
>>>
>>>Regards
>>>
>>>Roger Govier
>>>
>>>
>>>
>>>Tom Rogers wrote:
>>>
>>>
>>>>I have setup a spreadsheet to keep track of my personal bowling scores.
>>>>I have made everything automatic except 2 formulas that I cannot figure
>>>>out, can someone help?
>>>>
>>>>1) Series Total column. I have a row of 3 numbers that get a simple SUM
>>>>formula in the 4th column of that row. These series totals range in a
>>>>column from G4 - G32. I have conditionally formatted them so that if the
>>>>formula result is zero, paint the cell white so I don't see the zeros.
>>>>So in this formula column, I only get an actual formula result when I
>>>>actually have scores input in the rows. I want to find the MIN series
>>>>total in the entire column, but the MIN formula is giving me a zero
>>>>(because I have hidden zeros). How can I get the minimum actual number,
>>>>not being a zero.
>>>>
>>>>2) I need to figure high series WITH handicap added in. Say G8 has high
>>>>series number, I need to multiply J7 by 3 then add this to the high
>>>>series number in G8. How do I do this? I need the cell number for the
>>>>high series returned, so that I can point the J column cell # to one
>>>>less than the G column cell #.
>>>>
>>>>TIA,
>>>>
>>>>-Tom
>>>>
>>>>
>>>>
>>>>
>>>>
>>
>>
>>