Should be a simple formula.....Help!

KingofKings

New Member
Joined
Nov 26, 2005
Messages
1
Hello.
OK, what I'm doing is basically tallying up a series of tournaments in order to show overall winners. I have a formula to do that- that is no problem.

What I need to do however is for every one of these individual tournaments is to get a score. The first tournament there are 64 entrants and I want to gte a score out of 128.
The first person #1 should have a score of 127 (just to bring it on par with the other tournaments of 128 I am comparing it with.
The second: score 125
The second last: score 3
Last score 1 (just to make it one better that if the tournament was not entered at all.

To do this i have used the formula =129-1*(2*C5) (where C5 was their place in the tournament).

The problem I have is that I am using a bakers dozen numbers of tournaments and in each one slightly different people contested. Some people were in every tournaments, some were in a few and others may have only been in one.

The trouble I have is that when a person did not enter a tournament he gets a placing of "0", which means that point wise he gets 129, which is better than first place.
In actuality I want him to get 0. How can i put some kind of limit so that the calculation is only done if the placing is greater than 0 (ie 1 and up)? But if the placing is zero, then the points will come to be zero)?

Sorry if that was hard to follow, you probably didn't need to know the background of what I needed, but there it is.
Please help me someone.
Cheers.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Welcome to MrExcel Board!

You could use:
=(C5<>0)*(129-1*(2*C5))

But what about negative numbers? Numbers 65 and above?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,163
Members
430,343
Latest member
t0m_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top