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 in
excel.
Than I manually add it to a data sheet.
Is there a way to add it constantly and have an auto update that finds
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, third
gets 8 and Jim gets 7

So main board looks like this

Jim 7
Bob 14
Sue 8
Tom 9


--
phat al
------------------------------------------------------------------------
phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988
View this thread: http://www.excelforum.com/showthread...hreadid=567672

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:


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 in
excel.
Than I manually add it to a data sheet.
Is there a way to add it constantly and have an auto update that finds
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, third
gets 8 and Jim gets 7

So main board looks like this

Jim 7
Bob 14
Sue 8
Tom 9


--
phat al
------------------------------------------------------------------------
phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988
View this thread: http://www.excelforum.com/showthread...hreadid=567672

wow
that's great, can I ask another question ?
how can I get a static chart to read information linked with the name
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 chart
with 14? And so forth?


--
phat al
------------------------------------------------------------------------
phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988
View this thread: http://www.excelforum.com/showthread...hreadid=567672

Im not quite sure what u mean,- can u explain som more :-)



"phat al" skrev:


wow
that's great, can I ask another question ?
how can I get a static chart to read information linked with the name
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 chart
with 14? And so forth?


--
phat al
------------------------------------------------------------------------
phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988
View this thread: http://www.excelforum.com/showthread...hreadid=567672

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 have
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 who
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 have
a static chart with all there names on that updates as the stats change?


--
phat al
------------------------------------------------------------------------
phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988
View this thread: http://www.excelforum.com/showthread...hreadid=567672

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

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

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"excelent" wrote in message
...
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

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

check this webside out

http://www.cpearson.com/excel/rank.htm

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


--
phat al
------------------------------------------------------------------------
phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988
View this thread: http://www.excelforum.com/showthread...hreadid=567672