Help with formula

[OdAwG]

Hello All Excel Gurus

I was wondering if anyone could help me out. I am trying to automate a
manual process of running a bowling bracket sheet. I was able to compare to
scores and get the higher score, but, instead of the score, how can I get
the name associated with that score? Listed below is an example of the
bracket sheet with cell D5 and H5 having the formula enumerated.

1 2 3 4 5
A Name Score Name
B -----------------------------------------
C John 212
D =max(c3,e3) ' this will
give me 213, I would like the name
E Jane 213
F
G Hulk 189
H =max(g3,I3) ' this will
give me 200, I would like the name
I Hogan 200

Any and all help in this matter is greatly appreciated.

Argus

 

[OdAwG]

I figured it out, with the following:

=IF(MAX(D2,D6)=D2,A2,IF(MAX(D2,D6)=D6,A6))

Thanks all,

Argus

 

[Joel]

Use lookup combined with max

=LOOKUP(MAX(G2:G6),G2:G6,E2:E6)

column G contains the numbers, column E contains the names.

 

[OdAwG]

Another Question,

How can I randomize a list of names from a range and then put them in
specific cells,
eample:

1 2 3 4 5
A John
B Jane
C Joe
D Mark
E Robert
F Roy
G Bob
H Jason
I
J Randomize the above list
K and put the results in
L specific cells
M Jason
N Mark
O Joe
P Roy
Q Jane
R Robert
S John
T Bob

Any and all help in this matter is greatly appreciated.

Argus


U

 

[Damon Longworth]

A common method uses the Rand function in a column adjacent to your names.
Each time you sort on the Rand column, you will get a randomized list. You
can have formulas in your "specific cells" to this sortable list.

--

Damon Longworth

2007 East Coast Excel / Access User Conference
April 18-20, 2007 - Providing Microsoft Excel training and Microsoft Excel
Classes
Atlantic City, New Jersey
www.ExcelUserConference.com/ECEUC.html

Another Question,

How can I randomize a list of names from a range and then put them in
specific cells,
eample:

1 2 3 4 5
A John
B Jane
C Joe
D Mark
E Robert
F Roy
G Bob
H Jason
I
J Randomize the above list
K and put the results in
L specific cells
M Jason
N Mark
O Joe
P Roy
Q Jane
R Robert
S John
T Bob

Any and all help in this matter is greatly appreciated.

Argus


U

 

[OdAwG]

Hey Damon,

So, your saying then fro the example below the following:

1 2 3 4 5
A John =rand() 'This gives me a
decimal value
B Jane =rand() 'This gives me a
decimal value
C Joe =rand() 'This gives me a
decimal value
D Mark =rand() 'This gives me a
decimal value
E Robert =rand() 'This gives me a
decimal value
F Roy =rand() 'This gives me a
decimal value
G Bob =rand() 'This gives me a
decimal value
H Jason =rand() 'This gives me a
decimal value
I
J 'How do I associate the values from RAND() to the names so that
K 'I can assign them to a specific cell listed below
L
M Jason
N Mark
O Joe
P Roy
Q Jane
R Robert
S John
T Bob

Thanks for the help

 

[Chip Pearson]

Put random numbers in column D using the RAND() function and array enter the
following formula into the cells in which you want return the names. For
example, to return the name into range A21:A25, select those cells, type in
the formula, and press CTRL+SHIFT+ENTER. rather than just ENTER. This MUST
be entered into the result cells as an array formula.

Change the $D$1:$D$5 reference and the $E$1:$E$5 reference to refer to the
correct ranges. The "1:5" should be the number of elements to return.

=INDEX($E$1:$E$5,MATCH(LARGE($D$1:$D$5,ROW(INDIRECT("1:5"))),$D$1:$D$5,0),1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[OdAwG]

Mr. Chip,

It still does not work correctly, I can't seem to select the range (example
A6,A8,A10, etc..) or when I tried to select the first 4 rows, I get #N/A,
and lastly, if I just select 1 cell, it shows me the name:

A B C D
E F

1 Argus =rand()
2 Bob =rand()
3 Chip =rand()
4 David =rand()
5
'Pasted your formula here
6 =e6
your formula
7 =e7
your formula
8 =e8
your formula
9 =e9
your formula
10

Argus

 

[Ragdyer]

Don't know why you're examples have letters going down and numbers going
across. That's contrary to XL's normal Column and Row labeling

Say your list of 8 names is in Column E, from E1 to E8.
In F1 enter:
=Rand()
and copy down to F8.
This places a random number next to each name in a sort of datalist.

Now, enter this formula in the first cell that you wish to display the first
random name:

=INDEX($E$1:$E$8,RANK(F1,$F$1:$F$8))

And then copy it down 7 rows.

This will give you a random list of names.
Each time you hit <F9>, you'll get a new random list.

You can change the calc mode of this sheet to "manual", in order to retain
the present list of random names *until* you hit <F9>.

 

[David Biddulph]

You don't need your second IF statement, do you?

=IF(MAX(D2,D6)=D2,A2,A6)

 

[OdAwG]

Hey David,

Thanks for the reply. No I do not need the second IF, your example is alot
better. I like it.

One question, if both D2 and D6 is 0 then leave it blank ele the value. You
know what I mean

Argus

 

[David Biddulph]

=IF(AND(D2=0,D6=0),"",IF(MAX(D2,D6)=D2,A2,A6))

 

[Chip Pearson]

In your original question, you had the names in column E and the random
numbers in column D, and I wrote the formula for those columns. Now you have
the names in C and the numbers in D. No fair changing the rules midstream.
Try a formula like following:

=INDEX($C$1:$C$5,MATCH(LARGE($D$1:$D$5,ROW(INDIRECT("1:5"))),$D$1:$D$5,0),0)

Like previous formula, this formula MUST (!) be entered as a single array
formula (using CTRL+SHIFT+ENTER) into the cells that will contain the
results. You must enter this formula into all the result cells as single
CTLR+SHIFT+ENTER operation. It will not work if you enter it into one cell
and fill or copy down.

I wrote the original formula and the revised formula above in Excel 2007,
and it works as it should. However, when I tested it in Excel 2003 and Excel
97, I got #N/A errors. The reason for this is a quirk in the way Excel
calculates the sheet. To get around this, enter =RAND() in some column other
than D, say G1:G5, select and copy that range, select the range in column D,
then go to the Edit menu, choose Paste Special, and then Values. This will
paste the values of the RAND formulas in column G into D, and the formulas
will calculate properly. Whenever you want to re-randomize the list, copy
the values from G to D. A quick way to do this is select the values in G,
right-click and hold the Selection border, and drag the range from G to D.
When you release the right-click button, choose "Copy Here As Values Only".

I'm not sure why earlier versions would return #N/A errors. It works fine in
2007.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[OdAwG]

Hey Mr. "D"

That did not work. I am thinking since D2 and D6 have a formula in it, that
will not work since it is technically not empty
in D2 and D6 I have the following:

A B C D
1
2 =if(A2>0,sum(B2,c2),"")
3
4
5
6 =if(A6>0,sum(B6,c6),"")
7
8 'with your formula =IF(AND(D2=0,D6=0),"",IF(MAX(D2,D6)=D2,A2,A6))

It still shows a value in D8 even though their is nothing in D2, D6 except
the formula...

Argus

 

[David Biddulph]

Have a look at what your formula is putting into D2 and D6, and use that in
your test.

Test for "" instead of 0.
--
David

Hey Mr. "D"

That did not work. I am thinking since D2 and D6 have a formula in it,
that will not work since it is technically not empty
in D2 and D6 I have the following:

A B C D
1
2 =if(A2>0,sum(B2,c2),"")
3
4
5
6 =if(A6>0,sum(B6,c6),"")
7
8 'with your formula =IF(AND(D2=0,D6=0),"",IF(MAX(D2,D6)=D2,A2,A6))

It still shows a value in D8 even though their is nothing in D2, D6 except
the formula...

Argus

=IF(AND(D2=0,D6=0),"",IF(MAX(D2,D6)=D2,A2,A6))
--
David Biddulph

Hey David,

Thanks for the reply. No I do not need the second IF, your example is
alot better. I like it.

One question, if both D2 and D6 is 0 then leave it blank ele the value.
You know what I mean

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
You don't need your second IF statement, do you?

=IF(MAX(D2,D6)=D2,A2,A6)
--
David Biddulph

I figured it out, with the following:

=IF(MAX(D2,D6)=D2,A2,IF(MAX(D2,D6)=D6,A6))

Hello All Excel Gurus

I was wondering if anyone could help me out. I am trying to automate
a manual process of running a bowling bracket sheet. I was able to
compare to scores and get the higher score, but, instead of the
score, how can I get the name associated with that score? Listed
below is an example of the bracket sheet with cell D5 and H5 having
the formula enumerated.

1 2 3 4 5
A Name Score Name
B -----------------------------------------
C John 212
D =max(c3,e3) ' this
will give me 213, I would like the name
E Jane 213
F
G Hulk 189
H =max(g3,I3) ' this
will give me 200, I would like the name
I Hogan 200

Any and all help in this matter is greatly appreciated.

Argus

 

[OdAwG]

Hey Mr. "DB",

could not get it to work with your newest suggestion however, I did get it
to work using the following.

=IF(AND(ISNUMBER($D5)=FALSE,ISNUMBER($D9)=FALSE),"
",IF(ISNUMBER($D5)=FALSE,$A9,IF(ISNUMBER($D9)=FALSE,$A5,IF(MAX($D5,$D9)=$D5,$A5,$A9))))

I had to check for each cell to be a number first.

Thanks for the help, it's working like a charm. I just got one more hurddle
to work on.....

Argus

Have a look at what your formula is putting into D2 and D6, and use that
in your test.

Test for "" instead of 0.
--
David

Hey Mr. "D"

That did not work. I am thinking since D2 and D6 have a formula in it,
that will not work since it is technically not empty
in D2 and D6 I have the following:

A B C D
1
2 =if(A2>0,sum(B2,c2),"")
3
4
5
6 =if(A6>0,sum(B6,c6),"")
7
8 'with your formula =IF(AND(D2=0,D6=0),"",IF(MAX(D2,D6)=D2,A2,A6))

It still shows a value in D8 even though their is nothing in D2, D6
except the formula...

Argus

=IF(AND(D2=0,D6=0),"",IF(MAX(D2,D6)=D2,A2,A6))
--
David Biddulph

Hey David,

Thanks for the reply. No I do not need the second IF, your example is
alot better. I like it.

One question, if both D2 and D6 is 0 then leave it blank ele the value.
You know what I mean

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
You don't need your second IF statement, do you?

=IF(MAX(D2,D6)=D2,A2,A6)
--
David Biddulph

I figured it out, with the following:

=IF(MAX(D2,D6)=D2,A2,IF(MAX(D2,D6)=D6,A6))

Hello All Excel Gurus

I was wondering if anyone could help me out. I am trying to
automate a manual process of running a bowling bracket sheet. I was
able to compare to scores and get the higher score, but, instead of
the score, how can I get the name associated with that score?
Listed below is an example of the bracket sheet with cell D5 and H5
having the formula enumerated.

1 2 3 4 5
A Name Score Name
B -----------------------------------------
C John 212
D =max(c3,e3) '
this will give me 213, I would like the name
E Jane 213
F
G Hulk 189
H =max(g3,I3) ' this
will give me 200, I would like the name
I Hogan 200

Any and all help in this matter is greatly appreciated.

Argus

 

[OdAwG]

Mr. Chip,

I'm sorry, I was doing some cleanup in my spreadsheet and I didn't even
notice the change until you mentioned it.

Call me a dummy, but i'm very new to arrays in excel. I did what you
suggested:

001. I highlighted all the cells that I wanted the results in
002. I typed in your suggested formula
003. I hit the CTLR+SHIFT+ENTER keys

and nothing happens

What am I doing wrong?

Do I have to first have to enter the formulas into those cell first, then
Highlight it, and then hit CTRL+SHIFT+ENTER

I tried that to and same thing...

Argus

 

[Chip Pearson]

You don't need to ENTER the formula before CTRL+SHIFT+ENTER. Select the in
which the results are to be places, type the formula and press
CTRL+SHIFT+ENTER.

If you do this properly, Excel will display the formula enclosed in curly
braces. Also, it you attempt to modify a single cell within the result
range, you will get a "Cannot change part of array" error message. Are these
true for your worksheet? If either is not the case, then you have not
property array-entered the formula.

Beyond that, make sure that the ranges referenced in the formula contain the
proper values. The only way that formula will return empty cells is if it
was not properly entered as an array formula or that range referenced by the
formula contains empty cells.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[OdAwG]

I was trying to see if there was an IF EXIST formula to check to see if the
bracket sheet (Brkt1, Brkt2, and etc...) exist, if so, then do the following
below, but no such luck.

Formula for 1st Place is Max, and the 2nd place is Min
=IF(MAX(Brkt1!L8,Brkt1!L28)=Brkt1!L8,Brkt1!I8,Brkt1!I28)
=IF(MIN(Brkt1!L8,Brkt1!L28)=Brkt1!L8,Brkt1!I8,Brkt1!I28)

A B C
Brkt Name 1st-Place
1 Captain America $15.00
2 Wonder Woman $15.00
3 Wonder Woman $15.00
4 Hulk Hogan $15.00
5 Hulk Hogan $15.00
6 Sponage Bob $15.00
7 Spawn $15.00
8 Spongae Bob $15.00
9 Cleopatra $15.00
10 Cleopatra $15.00
11 Superman $15.00
12 #REF!
13 2nd Place is below
14 Scobe Doo $6.00
15 Captain America $6.00
16 Dark Angel $6.00
17 One Last Time $6.00
18 Batman $6.00
19 Spiderman $6.00
20 Cat Woman $6.00
21 Spiderman $6.00
22 King Kong $6.00
23 Mighty Joe $6.00
24 Spiderman $6.00
25 #REF!


How do I get the above listing in this format where the winner of a
particular bracket could have won 1st and 2nd
Example Captain America won 1st Place and 1 second place. The winnings for
1st place is $15 and the winnings for 2nd is $6 so the total for Captain
America is $21.00.

A B C
30 Captain America $21.00
31 Wonder Woman $30.00
32 Hulk Hogan $30.00
33 Sponage Bob $30.00
34 Spawn $15.00
35 Cleopatra $30.00
36 Superman $15.00
37 Scobe Doo $6.00
38 Dark Angel $6.00
39 One Last Time $6.00
40 Batman $6.00
41 Spiderman $18.00
42 Cat Woman $6.00
43 King Kong $6.00
44 Mighty Joe $6.00

Any and all help in this matter is greatly appreciated.

Argus