Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need help with two Bowling Formulas
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message . .. 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 |
#6
|
|||
|
|||
That did it! Thanx!
-Tom "swatsp0p" wrote in message ... 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 |
#7
|
|||
|
|||
Thanx, got it working!
-Tom "Roger Govier" wrote in message ... 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" wrote in message .. . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
locking formulas?? | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |