Junior
Regular
Posts: 800
HEEE HAAWWW
|
 |
« on: May 21, 2011 at 05:01 » |
|
Hey guys, I'm trying to figure out a formula that will put a value on a cell that is greater than 0. I know I can do this with conditional formatting but i'm trying to make my points sheet update the number of tournaments a player has played in automatically after I enter their score each time. Here's a cut out of the sheet. I want the #trn column to update each week I enter a score with a value of 1. Can someone help me with this? Thank you  |
|
|
|
|
Logged
|
The Donkey formerly known as Shmegma!
|
|
|
William
Regular
Posts: 2303
Bloody Marvellous
|
|
« Reply #1 on: May 21, 2011 at 06:02 » |
|
=count(D3:R3)
|
|
|
|
|
Logged
|
I'll play you in any game you name for any amount you can count.
|
|
|
Junior
Regular
Posts: 800
HEEE HAAWWW
|
|
« Reply #2 on: May 21, 2011 at 09:11 » |
|
wow, that simple? Dammit, why couldn't I find that? lol
Thanks a lot William! Cheers
|
|
|
|
|
Logged
|
The Donkey formerly known as Shmegma!
|
|
|
William
Regular
Posts: 2303
Bloody Marvellous
|
|
« Reply #3 on: May 21, 2011 at 12:06 » |
|
Sometimes the simple answers stare us right in the face  |
|
|
|
|
Logged
|
I'll play you in any game you name for any amount you can count.
|
|
|
Junior
Regular
Posts: 800
HEEE HAAWWW
|
|
« Reply #4 on: May 21, 2011 at 16:00 » |
|
I entered the formula and it works, however it counts every entry and I only want it to count cells that are greater than 0.00 so that it only counts the ones where a player has a score entered?
|
|
|
|
|
Logged
|
The Donkey formerly known as Shmegma!
|
|
|
Junior
Regular
Posts: 800
HEEE HAAWWW
|
|
« Reply #5 on: May 21, 2011 at 16:20 » |
|
i just looked at a few sites and read about the COUNTIF function but it seems to only allow you to count a cell if it equals a specific number. I cannot find a criteria to enter for a value greater than 0. I always have all the cells with a 0.00 value entered so that when we reach the point in the season where low scores are dropped, those cells with 0's automatically are dropped with the conditional formatting.
|
|
|
|
|
Logged
|
The Donkey formerly known as Shmegma!
|
|
|
|
plesner
|
|
« Reply #6 on: May 21, 2011 at 16:26 » |
|
Haven't used Excel for several years but if I remember correctly
=COUNTIF(D3:R3,">0")
|
|
|
|
|
Logged
|
Who needs balls when you've got the nuts?
|
|
|
Junior
Regular
Posts: 800
HEEE HAAWWW
|
|
« Reply #7 on: May 21, 2011 at 17:04 » |
|
Haven't used Excel for several years but if I remember correctly
=COUNTIF(D3:R3,">0")
I have tried that formula and it did not work. I entered <0 because I want it to count scores greater than 0 but yet it still didn't work? I'm stumped. Read another tip somewhere that said to enter <0 into another cell and then refer to that cell when selecting the criteria, and yet that one didn't work either. I should note also that I'm still using Excel 2000 however I would assume that because the countif funtion is still there that this formula should work? |
|
|
|
|
Logged
|
The Donkey formerly known as Shmegma!
|
|
|
|
plesner
|
|
« Reply #8 on: May 21, 2011 at 19:00 » |
|
If you want to count those greater than zero, you most certainly need to enter ">0" as '>' means 'greater than'.
Edit: Just booted up an old computer with an Excel 2000 installed. The formula I suggested works.
|
|
|
|
« Last Edit: May 21, 2011 at 19:05 by plesner »
|
Logged
|
Who needs balls when you've got the nuts?
|
|
|
William
Regular
Posts: 2303
Bloody Marvellous
|
|
« Reply #9 on: May 22, 2011 at 12:42 » |
|
Tested plesner's formula in Excel 2007, and it works fine there too.
> means that the value to the left is greater than the value to the right: 3>0 = true
< means the value to the left is smaller than the value to the right: 3<0 = false
|
|
|
|
« Last Edit: May 22, 2011 at 12:44 by William »
|
Logged
|
I'll play you in any game you name for any amount you can count.
|
|
|
Dr. Neau
Regular
Posts: 9659
Dr. Neau is a player of the pokers
|
|
« Reply #10 on: May 22, 2011 at 23:20 » |
|
The shark eats the bigger number.
|
|
|
|
|
Logged
|
(not a real doctor) Concentrate on winning your tournament...let Dr. Neau manage it. http://drneau.com |
|
|
Junior
Regular
Posts: 800
HEEE HAAWWW
|
|
« Reply #11 on: May 23, 2011 at 15:25 » |
|
ah, thanks guys, preciate it. I thought it worked the other way around! :}
|
|
|
|
|
Logged
|
The Donkey formerly known as Shmegma!
|
|
|
Wedge Rock
Global Moderator
Regular
Posts: 9471
CC>CC # R-7604
|
|
« Reply #12 on: May 23, 2011 at 16:55 » |
|
Here's something to think about.
You could use the first formula [count(D3:R3)] and the second formula with the "=" limitation [countif(D3:R3,0)] and get the result you wanted.
In essence, you count the number of all the cells [count(D3:R3)] and then subtract out the number of cells that are equal to zero [countif(D3:R3,0)] and you end up with the number of cells that are not zero. (Of course, this formula would also count cells that had a value of "less" than" zero, in theory...although in practical application, that doesn't look to be an issue.)
|
|
|
|
|
Logged
|
Wedge Rock (not a real rock)  Guilty of over-using ellipses... |
|
|
Junior
Regular
Posts: 800
HEEE HAAWWW
|
|
« Reply #13 on: May 23, 2011 at 18:09 » |
|
Thanks Wedge. that too sounds like it would work. We're still loving those Bellagio ceramics in our tourneys. Glad we picked them up from you.
|
|
|
|
|
Logged
|
The Donkey formerly known as Shmegma!
|
|
|
Wedge Rock
Global Moderator
Regular
Posts: 9471
CC>CC # R-7604
|
|
« Reply #14 on: May 23, 2011 at 21:02 » |
|
When you get it doing what you want, email it to me and I'll add some vlookup formulas that will auto update the order...
I miss the Bellagio ceramics... That was a nice set.
|
|
|
|
|
Logged
|
Wedge Rock (not a real rock) Guilty of over-using ellipses... |
|
|
|
