Thursday, November 10, 2011

yogi_Assign 1 If A Person Submits Entry By DeadLine Else Assign 0

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
user Estesark said:
How do I check for the last entry before a deadline?

I have a form, which people on a forum I visit use to submit answers. I have multiple sheets on the form spreadsheet to manipulate the data, but there is one thing I can't quite work out how to do: find the last entry before a deadline for each person. I need to be able to do this because if a person submits a new set of answers, the old ones are irrelevant. Because all the entries are timestamped, I can check whether they were submitted before a deadline, which I enter manually into a cell, using a simple formula such as IF(C3<=$C$2,1,0), where column C contains the dates and cell C2 contains the deadline.I can also check whether they submitted any answers afterwards with formulas likeIF(COUNTIF(E5:$E$20,E4)=0,1,0), where column E contains their names.What I'm not sure about is how to combine the two, to give me a "1" value for the last entry submitted by each person before the deadline, and "0" values for earlier entries and entries after the deadline. I can't just multiply the two IFs together; I feel like there has to be some sort of lookup involved, but I'm not sure what it is.Please let me know if you need me to provide more detail. Thank you!
following is one solution to the problem