How should I do it?

P

phat al

I have a list of six people who total different scores every day.
I need to add the data to a chart based on who is on top and so forth.
Currently I add there totals and then sort by highest to lowest i
excel.
Than I manually add it to a data sheet.
Is there a way to add it constantly and have an auto update that find
the top and seconds and so forth.
Eg
Jim 100
Bob 4500
Sue 3000
Tom 3500

Will look like this after
Bob 4500
Tom 3500
Sue 3000
Jim 100

First place gets 14 points to main score, second gets 9 points, thir
gets 8 and Jim gets 7

So main board looks like this

Jim 7
Bob 14
Sue 8
Tom
 
E

excelent

if names in a2:a7

C2=LARGE($B$2:$B$7,ROW()-1)
D2=INDEX($A$2:$A$7,MATCH(C2,$B$2:$B$7,0))
E2=14 E3=9 E4=8 E5=7 E6=? E7=?
F2=C2+E2

insert all formulas and copy down to row 7


"phat al" skrev:
 
P

phat al

wow
that's great, can I ask another question ?
how can I get a static chart to read information linked with the nam
only?
if I had ten different charts similar to the above one
what formula would look for given name of say person on top of char
with 14? And so forth
 
P

phat al

not sure why this wont work? it worked in c2 to c7?
all i did was move it down the page to 56 to 63?

=LARGE($B$56:$B$63,ROW()-1) gives #num

=INDEX($A$56:$A$63,MATCH(C56,$B$56:$B$63,0)) gives #num

as for the second question i wanted the person who got the most to hav
14 points added to the master chart and the rest following in order
this will change daily but a snap shot is used to motivate them on wh
is in the lead in many areas.
how do i collect that data from your sum in areas C D E 2 to 7 and hav
a static chart with all there names on that updates as the stats change
 
E

excelent

ROW()-55 = 1 = the largest value

=LARGE($B$56:$B$63,1) where 1 stands for the largest value
=LARGE($B$56:$B$63,2) where 2 stans for the second large value
so instead of writing all formulas manuels i use the ROW() function as counter

so when ur data starts in ROW 56 u have to substract 55 to get 1 :

=LARGE($B$56:$B$63,ROW()-55)

The MATCH formula should be ok when the first is
 
R

Ragdyer

This will *start* with 1 *anywhere* you enter it, and increment as you copy
down:

=LARGE($B$56:$B$63,ROWS($1:1))
 
E

excelent

try have Names to the left and value to right
then select Names and values and then make ur chart

C56=INDEX($A$56:$A$63,MATCH(D56,$B$56:$B$63,0))
D56=LARGE($B$56:$B$63,ROW()-55)
copy down

By the way if 2 names have same value then the first name showing twise

im trying to find a way to fix this
 
P

phat al

thank you for your time
that worked for moving the sum around, great stuff
and yes if there is a fix to the name duplicated due to same amount
will be great, however you have saved me so much time
i will look into this web site you have posted
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top