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
|