Returning multiple values in formula

[bjornishungry]

Hi! I'm looking to create a formula for a contest spreadsheet that will tell if a given contest winner has won anything in the last 30 days (if so, they are ineligible to win until a month has passed and a cell in the spreadsheet should reflect that). It seems like a fairly complex formula would be required to do this, so I've created a column for each prize to reflect whether it was won in the past 30 days. An idea I had was to have this column in 0's and 1's, and sum the total for each winner (if sum>0 ineligible else eligible). There's still several issues that need to be resolved in addition to finding a working formula to do this: excluding the current row from consideration (my idea was setting the period 1-30 days as opposed to 0-30 days), and making the formula work for a variable number of columns. I'm stuck on the nested IF statement to return the multiple corresponding eligibility cells by winner name. Another issue then is whether the spreadsheet would be too slow when a large number of winners are stored in it. Help would be appreciated!

>30 Days formula (working):
=IF(DATEDIF(Date,NOW(),"D")<30,"N","Y")

<table border="0" cellpadding="0" cellspacing="0" width="256"><colgroup><col style="width:48pt" width="64"> <col style="width:48pt" span="3" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:48pt" height="17" width="64">Date</td> <td style="width:48pt" width="64">Name</td> <td style="width:48pt" width="64">Eligible?</td> <td style="width:48pt" width="64">>30 Days?</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">5/14/11</td> <td>Smith</td> <td>N
</td> <td>N</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">5/9/11</td> <td>Anderson</td> <td>N
</td> <td>N</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">4/21/11</td> <td>Johnson</td> <td>Y
</td> <td>Y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>Y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>Y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">11/8/10</td> <td>Smith</td> <td>N
</td> <td>Y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">9/15/10</td> <td>Johnson</td> <td>Y
</td> <td>Y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>Y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">1/28/11</td> <td>Brown</td> <td>Y
</td> <td>Y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">2/17/11</td> <td>Smith</td> <td>N
</td> <td>Y</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" align="right" height="17">6/1/11</td> <td>Anderson</td> <td>N
</td> <td>N</td> </tr> </tbody></table>
Hope I've explained my question clearly enough, let me know if anything needs more clarification.

Edit: the spreadsheet is designed to be used by many different people, so I wish to avoid changing settings in Excel that would make the formula work on one computer but not on others.

 

[Akashwani]

Hi, Welcome to MrExcel.

Could you provide some more sample data? You have a better chance of getting the right answer if you can show a sample of your data and the expected results.

You say that you have created a column for each prize, where are these columns? How do you know if the prize has been won?

This may help with the sample data you have provided so far....

Excel Workbook
	A	B	C	D	E
1	Date	Name	Eligible?	*	04/06/2011
2	14/05/2011	Smith	N	*	*
3	09/05/2011	Anderson	N	*	*
4	21/05/2011	Johnson	N	*	*
5	11/05/2011	Smith	N	*	*
6	20/04/2011	Johnson	Y	*	*
7	30/04/2011	Brown	Y	*	*
8	06/05/2011	Smith	N	*	*
9	01/06/2011	Anderson	N	*	*
Sheet3

Ak

 

[bjornishungry]

Akashwani,

The winner would be in another column, and that data would be entered manually. You know if the prize has been won if there is a winner listed for a given prize, else those fields would be blank. There are of course more columns for contact info etc, but the other fields are unrelated to the eligibility column. The expected results are those currently in the "Eligible?" column. I'd use the add-on you provided for posting the table in the forum, but that will have to wait until I'm on my other computer.

<table border="0" cellpadding="0" cellspacing="0" width="625"><col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:7753;width:159pt" width="212"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:67pt" height="17" width="89">*</td> <td style="width:61pt" width="81">A</td> <td style="width:65pt" width="87">B</td> <td style="width:69pt" width="92">C</td> <td style="width:48pt" width="64">D</td> <td style="width:159pt" width="212">E</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">1</td> <td>Date</td> <td>Name</td> <td>Eligible?</td> <td class="xl65">>30 Days</td> <td>Prize</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">2</td> <td class="xl66">5/14/11</td> <td>Smith</td> <td>N</td> <td>N</td> <td>$25 Lottery Tickets</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">3</td> <td class="xl66">5/9/11</td> <td>Anderson</td> <td>N</td> <td>N</td> <td>$15 Erberts & Gerberts Gift Card</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">4</td> <td class="xl66">4/21/11</td> <td>Johnson</td> <td>Y</td> <td>Y</td> <td>$20 Cash</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">5</td> <td>
</td> <td>
</td> <td>
</td> <td>Y</td> <td>$25 Cash</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">6</td> <td>
</td> <td>
</td> <td>
</td> <td>Y</td> <td>$50 Cash</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">7</td> <td class="xl66">11/8/10</td> <td>Smith</td> <td>N</td> <td>Y</td> <td>Baseball Tickets</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">8</td> <td class="xl66">9/15/10</td> <td>Johnson</td> <td>Y</td> <td>Y</td> <td>$25 Lottery Tickets</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">9</td> <td>
</td> <td>
</td> <td>
</td> <td>Y</td> <td>$15 Erberts & Gerberts Gift Card</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">10</td> <td>
</td> <td>
</td> <td>
</td> <td>Y</td> <td>$25 Lottery Tickets</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">11</td> <td class="xl66">1/28/11</td> <td>Brown</td> <td>Y</td> <td>Y</td> <td>$15 Erberts & Gerberts Gift Card</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">12</td> <td class="xl66">2/17/11</td> <td>Smith</td> <td>N</td> <td>Y</td> <td>$20 Pizza Hut Gift Card</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17">13</td> <td class="xl66">6/1/11</td> <td>Anderson</td> <td>N</td> <td>N</td> <td>$25 Cash</td> </tr> </tbody></table>

 

[Akashwani]

Hi,

I have run into the same problem you had with multiple names and trying to return a N for them, see column G....

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Name	Eligible?	>30 Days	Prize	05/06/2011	Incorrect Formula
2	14/05/2011	Smith	N	N	$25 Lottery Tickets		N
3	09/05/2011	Anderson	N	N	$15 Erberts & Gerberts Gift Card		N
4	21/04/2011	Johnson	Y	Y	$20 Cash		Y
5				Y	$25 Cash
6				Y	$50 Cash
7	08/11/2010	Smith	N	Y	Baseball Tickets		Y
8	15/09/2010	Johnson	Y	Y	$25 Lottery Tickets		Y
9				Y	$15 Erberts & Gerberts Gift Card
10				Y	$25 Lottery Tickets
11	28/01/2011	Brown	Y	Y	$15 Erberts & Gerberts Gift Card
12	17/02/2011	Smith	N	Y	$20 Pizza Hut Gift Card		Y
13	01/06/2011	Anderson	N	N	$25 Cash		N
Prizes

I know there is a solution, but at this moment in time my brain is not engaging, I will continue to work on this, but in the mean time hopefully someone with the solution will post it.

Ak

 

[bjornishungry]

Well I've made some progress on the formula, however I still don't know how to get it to do quite what I want. The formula should adjust automatically as new entries are entered, as in B:B rather than B$2:B$12. I'm also concerned whether it will be too much information for the computer to process with larger table sizes, but I guess we'll find out later on that. The "WantedFormula" is what I would want the formula to do, but does not. Using the ">30 days formula" doesn't seem to work properly, I suspect something along the lines of cell formatting.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Date	Name	Eligible?DesiredResult	>30 Days Formula	Prize		>30 Days?	EligibleFormula	SUMIF	WantedFormula
2	5/14/11	Smith	N	1	$25 Lottery Tickets		1	N	1	Y
3	5/9/11	Anderson	N	1	$15 Erberts & Gerberts Gift Card		1	N	2	Y
4	4/21/11	Johnson	Y	0	$20 Cash		0	Y	0	Y
5				0	$25 Cash		0		0
6				0	$50 Cash		0		0
7	11/8/10	Smith	N	0	Baseball Tickets		0	N	1	Y
8	9/15/10	Johnson	Y	0	$25 Lottery Tickets		0	Y	0	Y
9				0	$15 Erberts & Gerberts Gift Card		0		0
10	1/28/11	Brown	Y	0	$25 Lottery Tickets		0	Y	0	Y
11	2/17/11	Smith	N	0	$15 Erberts & Gerberts Gift Card		0	N	1	Y
12	6/1/11	Anderson	N	1	$20 Pizza Hut Gift Card		1	N	2	Y
Sheet1

Thanks for the help
-Bjorn

 

[Akashwani]

Hi Bjorn,

I'm confused, I thought that you wanted the formula to do what you have achieved in H2:H12! You now seem to want a formula to do something else J2:J12 or have I misunderstood? It looks like your formula in D2 down is incorrect, you appear to be putting 1 and 0 in as text...

Is this not what you wanted to achieve?....

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Name	Eligible?	>30 Days	Prize	07/06/2011	*
2	14/05/2011	Smith	N	N	$25 Lottery Tickets	*	1
3	09/05/2011	Anderson	N	N	$15 Erberts & Gerberts Gift Card	*	1
4	21/04/2011	Johnson	Y	Y	$20 Cash	*	0
5	*	*	*	*	$25 Cash	*	0
6	*	*	*	*	$50 Cash	*	0
7	08/11/2010	Smith	N	Y	Baseball Tickets	*	0
8	15/09/2010	Johnson	Y	Y	$25 Lottery Tickets	*	0
9	*	*	*	*	$15 Erberts & Gerberts Gift Card	*	0
10	*	*	*	*	$25 Lottery Tickets	*	0
11	28/01/2011	Brown	Y	Y	$15 Erberts & Gerberts Gift Card	*	0
12	17/02/2011	Smith	N	Y	$20 Pizza Hut Gift Card	*	0
13	01/06/2011	Anderson	N	N	$25 Cash	*	1
Prizes

You seem to have a better understanding of your problem than I do and your formulas (slightly changed) seem to do what you require. I don't think that you will have any noticeable problem with these formulas recalculating over a large range.

Sorry I couldn't be of more help to you.

Ak

 

[bjornishungry]

Ak,

It works! You were right that the 1 and 0 were being entered incorrectly, and that solved the problem. Now the formula
=IF(B2="","",IF(SUMIF(B:B,B2,D:D)>0,"N","Y")).
does give the desired results. Thanks for all the help!

Bjorn

 

[Akashwani]

Hi Bjorn,

Thanks for the feedback, but there's no need to thank me, you did the hard work (well done), I just spotted a small error.

Good luck.

Ak