Sum of Cells with a Variable Cell Multiplier

Hi, all.

I've just found this forum and think it must be the best place to find an answer for my problem. I'll admit, I haven't searched the forum a lot for my answer because I really have no idea where I would start.

Here is my situation. I am trying to set up a spread sheet to score individuals in a timed challange.

This is how it works. Every individual starts with 50 points. For each challange, the score is determined on a point deduction system.
- For every second under the time limit, they gain a point.
- For every second over the time limit, they loose 3 points.
- For every missed objective, they loose 8 points.

I would like to simply enter each individual's time and missed objectives. I will use this for many different challanges, so I will occasionally change the time limit for each different challange. I have tried to write out a basic formula for how I would like this to work incase I did not do an adequate job of explaining:

Cell-0 = Time Limit
Cell-1 = Time Used
Cell-2 = Missed Objectives
Cell-3 = Total Score

example1:
cell0 = 50
cell1 = 52
cell2 = 1
cell3 = ?

example2:
cell0 = 50
cell1 = 45
cell2 = 2
cell3 = ?

example3:
cell0 = 50
cell1 = 50
cell2 = 0
cell3 = ?

if cell1>cell0
- then cell3 = 50 - 3*(cell1-cell0) - 8*cell2
if not
- then cell3 = 50 - (cell1-cell0) - 8*cell2

example1:
cell3 = 36

example2:
cell3 = 39

example3:
cell3 = 50

Thank you all for your help in advance. I trully appreciate it.

I tried that just now:

=if(B12>B11;50-3*(B12-B11)-8*B13;50-(B12-B11)-8*B13)

and it gives me an error. Any ideas?

it appears that the logical test is the problem. The examples that Office Help gives all have actual values to compare with, rather than cell to cell. Could that be the problem?

lol. I figured it out. It is the ";". It has to be a ",". So silly, a semi-colon vs a comma is the problem. zbor, thanks a ton. That was a very quick solution.

Yes, just wanted to write that

But do you get correct solution?

Quick addition though. Is there a way for the cell to only display a score if the time and missed objective cells are filled, otherwise be left blank?

=if(or(d4="";d5="");"";if(d5>d4;50-3*(d5-d4)-8*d6;50-(d5-d4)-8*d6))

Originally Posted by zbor

=if(or(d4="";d5="");"";if(d5>d4;50-3*(d5-d4)-8*d6;50-(d5-d4)-8*d6))

Once again, I had to change the ";" to ",". However, it worked great. Thanks for the quick and great responces. You've been a huge help.

Alright, my spreadsheet looks great. However, I'd like to add a new, unplanned, piece. When each individual completes the challange, they are awarded with either a Bronze, Silver, Gold, or nothing medal, depending on their score. I have already added a column next to the final score column that uses multiple nested "IF" statements to label what, if any, award the individual has earned. I'd like to add a small section to the right of my chart that will have headings of Bronze, Silver, and Gold. Then, I'd like it to list the appropriate candidates under their respective award (nothing if they dont earn any). I hope this makes sense. I feel like it should be somewhere in the Conditional Formating or Formulas section. Any help would again be greatly appreciated. Thanks.

untitled.jpg

Is this good enough?
Select cells -> conditional formating...

Actually, here is a picture of what I would like it to look like. You can see some examples plugged in. I would like the candidates that earn "Gold" to be listed as such on the right and the same for each of the other awards. I will make that part look decent later; for now, I just want to figure out how to output only those who earned gold into the list, and the same for silver and bronze.

Try this:

=LOOKUP(RANK(N8;$N$8:$N$17;0);{1;2;3;4};{"GOLD";"SILVER";"BRONZE";""})

This will give you gold, silver and bronze results but then you'll have to do conditional formating by this words and give them colors...

I don't think that will work. Here's a little more details on what I need:

Candidate names are listed from D8-D15.
Candidate Awards are listed from O8-O25.

For the Gold list, starting at R8, I need every Candidate names to be listed that has achieved that award. So, basically, I need to construct a list of names from the candidate list that have the correct award listed in the award list.

Is this possible? I will do some more searching on my own, but I thought I'd go ahead and pose the question incase I don't come up with anything.

Well, for beggining, upload here an worksheet example... It's easier and quicker that way..

Sorry, I thought about posting it to begin with, but wasn't sure if that would just be a waste of space on the forum. Here you go.

[QUOTE=zbor;2082366]Where you enter other matches so you can summ all gold medals etc.. or create list of winners ...
[QUOTE]

How do I create the list of winners? i.e. a list of winners of gold, silver, or bronze.

Side note: the names and scores I've entered are all just for example as of yet. When I put this sheet into use, there will likely be 10+ names listed. It might be best to have more names to confirm that it is working correctly.

I've found one very simple way of doing this, but not sure if it will work. I just entered =IF(O8="Gold",D8,"") into the cell. Problem is, then, there are a lot of empty cells in each column. I know I've seen somewhere that I can remove empty cells, but I cannot remember how. Also, I'm not sure if it will work since I will be constantly updateing data and that would create a problem with removing the empty cells.

Try this in R8...

=INDEX($D$8:$D$25;MATCH(R$7;$O$8:$O$25;0);1)

That does help. However, the problem is that it only returns one name. For the example I gave you, it's perfect. However, if I have more than one candidate earn a Gold award, it only lists the first name. I tried ctrl+Down to see if that would list more names, but it does not unfortunately. You will see a combination of two different methods. Your most recent method is at the top. The one at the bottom is more of a Band-Aid solution (temporary). Tell me what you think - do you think there is a why of listing more than one per column. I suppose it may have been confusing cause gold does not represent first place from the challenge, it just means that the candidate achieved a Gold Award worthy score. That means that everyone could "potentially" earn a gold award.

Thanks again for your hard work. I assure you, I am also trying to find ways of doing this. I have just been striking out left and right.

No potential solutions?

Got it. Thanks for the help. With the help of a friend, we've determined a different way to do it. If you'd like to see my solution, just post here or PM me.