In Excel, I am trying to complete a function for a bowl game(look below)?

Question

So basically there are 5 people in it, each person picks points (up to 24) 24 being the team that they think stands the most chance to win and 1 being the least chance to win. If team loses then they lose those points but if team wins they gain those points. How can incorporate this in excel?

Answer 1

you dont say how you have your data setup.
ive done something similar.
i will just use 4 teams and set up an example. you can expand it to 24.

1st we need a column of your teams
cell
a3=rams
a4=49ers
a5=falcons
a6=saints

2nd we need a row of the people
b2=veranodu
c2=merlin
d2=deacon
e2=rosie
f2=lamar

3rd we need the choices
down the b column drop veranodus numbers next to the team 1 thru 4. same with merlin in the c column, and so on

then we need the results-
copy the teams in the A column and paste into the H comlumn

cell
i2=veranodu
j2=merlin
k2=deacon
l2=rosie
m2=lamar

i just picked column P to the right.
P3=winners
Q3=losers
P5=rams
Q5=49ers
P6=falcons
Q6=saints

in other words the rams beat the 49ers and falcons beat saints

use this formula in i3
=IF(1=COUNTIF($P:$P,$H3),+B3,-B3)

you should be able to copy it down and over
then do your sums at the bottom.

let me know if i missed anything.