if with a twist

Posted by Allan on February 04, 2001 6:16 PM

I have one column that contains names, beside
each name are scores, which are updated from entries
on other sheets in the workbook.
Frank 10
Sam 6
Henry 5
Etc 3
I also have a list of prizes that are available,
on the same sheet, no matter the out come,
each participant would receive a prize

First place watch
2nd ring
3rd chain
etc

what I would like to happen is that the prize that
each person would receive change out as their score
changes....and the prize available for them be listed
by their name

one thing.....the prizes, would be changed out
with out going to change it in a formula

I have tried min....max from the list...I guess a
few things....but can't get it going
hope this makes sense and thanks in advance

Posted by Dave Hawley on February 04, 2001 7:11 PM

Hi Allan

Assuming your scores are in B2:B100, put this in cell C2 and copy down:

=IF(B2=LARGE($B$2:$B$100,1),"Watch",IF(B2=LARGE($B$2:$B$100,2),"Ring",IF(B2=LARGE($B$2:$B$100,3),"Chain","")))

You could also sort your scores with the names and have the prizes in cell C2:C3.

Dave

OzGrid Business Applications

Posted by Allan on February 05, 2001 5:34 AM

Thanks Dave
that works....is there away in the formula that
instead of typing in the prize....the text value
of the prize cell will come in by itself
eg:
=IF(B2=LARGE($B$2:$B$100,1),"D2",IF......
where D2 is the name of the first prize "watch"
the people who will be working with this do not
know how to change the formula out....smiles
thanks again

Posted by chance on February 05, 2001 6:16 AM

Posted by Allan on February 05, 2001 6:39 AM

thanks chance and Dave
perfection....works great
Allan

Posted by Aladin Akyurek on February 05, 2001 11:17 AM

I'll assume the names in A from A1 on and the scores associated with in B from B1 on. Select the cells with scores and name it SCORES via the Name Box (or Insert,Define,Name).

Create a table by entering say in E from E1 on the numbers 1,2, ...n (n is the number of prizes) and in F from F1 on the prizes themselves (watch, chain, parfume, etc). Select all these values and name the range they occupy PRIZES via the Name Box (or Insert,Define,Name).
Then enter

C1 =VLOOKUP((RANK(B1,SCORES,0)),PRIZES,2,0) [ copy down as far as needed ]

Note that the above formula allows 2 or more names to have the same prize, which is, it seems to me, more reasonable.

Aladin

Posted by Allan on February 05, 2001 9:17 PM

I came back to post the formula
I was having problems with and tried this
also works great.....does the trick
thank you very much
Allan