software

I have an flyer quiz incentive program that works like this.
$10 per participant per month in a monthly pool
$10 per participant per quarter in a quarterly pool.
$10 if someone gets a perfect score (if 2 people get a perfect score
they split the monthly pot and the $10.)
The quarterly pot goes to the employee(s) with the highest quarterly
score.
Each month there's a prize for the highest monthly score and each
quarter ther's an additional prize for the highest quarterly prize. For
example, if I had 3 employees each month participate, the monthly prize
would be $30. ($10X3), the quarterly prize would be $90 ($10x3x3).

I'd like some help organizing the data in the worksheet. I've attached
the spreadsheet. What I'd like to do is use VLOOKUP in the Winner range
looking at the scores in the TEST SCORE range using the criteria above.
If an employee does not participate the Pot decreases. I'd like the
Winner appear with the amount he will receive. If there is a tie the
amounts are split among the winner(s). Thanks -------------------------------------------------------------------
|Filename: FLYER QUIZZ.zip |
|Download: www.excelforum.com/attachment.php?postid=4497 |
-------------------------------------------------------------------

--
nander
------------------------------------------------------------------------
nander's Profile: www.excelforum.com/member.php...foamp;userid=6156
View this thread: www.excelforum.com/showthread...hreadid=524966
In the winner section below. What formula would I use to award the
person with the hightes score, Roger, the March pot of $70.00

Store 244 MAR
Roger $10
Andrew $10
Mark $10
David $10

Store 245
Mickey $10
Danny $10
Donald $10
$70

TEST SCORES
Store 244 MAR
Roger 95
Andrew87
Mark80
David93Mickey 90
Danny86
Donald88

WINNER
Store 244
Roger (I'd like a formula in each cell by each name that returns the
total value of the monthly pot if that person has the highest score.
But if 2 or more employee(s) ties they split the pot.
Andrew
Mark
David

Store 245
Mickey
Danny
Donald--
nander
------------------------------------------------------------------------
nander's Profile: www.excelforum.com/member.php...foamp;userid=6156
View this thread: www.excelforum.com/showthread...hreadid=524966