Adding or subtracting to a total after a specific date under certain conditions

Question

Kscheif1983 Author

Level 1

8 points

Adding or subtracting to a total after a specific date under certain conditions

I'll try to put this as easy as I can. I'm in a football pool this season and I'd like to keep track of my totals in Numbers. I pick 5 games a week. If I get 1 pick correct, I get 1 point, 2 picks, 2 points, etc.

However, if I get 5 picks correct, there is 1 bonus point added to 5.

I've figured it out up to this point.

Here is where I'm having trouble. If I get zero picks correct for the week, I get a -1 for the week. Since none of the boxes will be checked before the week starts, I don't want it to start out with a total of -1. I want it to show 0. The first week is over on 9/14/15. How can I get my total to show -1 only after the week is over if I didn't get any picks correct? I hope this makes sense. I've been at this for almost an hour and I've gotten close, but can't seem to get it right.

MacBook Pro (Retina, 13-inch, Mid 2014), OS X Yosemite (10.10.2)

Posted on Aug 27, 2015 6:11 PM

Reply

Answer 1

Best reply

charles.christian14

Level 1

147 points

Aug 27, 2015 8:20 PM in response to Kscheif1983

You would need to add an If formula similar to this

IF(countif(correct, true)>0, countif(correct, true), -1)

this should take care of your problem. This will count all the True answers, your checks, if this number is greater than 0 then it will display that number, but if it is equal to 0, then it will give -1.

Reply

Answer 2

t quinn

Level 5

7,043 points

Aug 27, 2015 9:42 PM in response to Kscheif1983

HI Kscheif,

Using your table as a guide, here is my formula for your tally cell. You may have to tweak the COUNTIF for the right cells.

IF(COUNTIF(C2:C6,TRUE())>4,COUNTIF(C2:C6,TRUE())+1,IF(COUNTIF(C2:C6,TRUE())>0,CO UNTIF(C2:C6,TRUE()),IF(TODAY()>H2,−1,0)))

H2 is my date cell.

This will show positive scores in real time, add +1 for 5 correct and subtract 1 if today is later than h2 and there are no correct.

quinn

Reply

Answer 3

Aug 27, 2015 9:45 PM in response to t quinn

Quinn is more correct, I forgot about the +1 for all five correct.

Reply

Answer 4

Kscheif1983 Author

Level 1

8 points

Aug 28, 2015 10:56 AM in response to t quinn

Thank you both for your help! That worked.

Reply

Answer 5

t quinn

Level 5

7,043 points

Aug 28, 2015 11:00 AM in response to Kscheif1983

HI Kscheif,

Glad to do it.

quinn

Reply

Answer 6

Aug 28, 2015 7:08 PM in response to Kscheif1983

I know that this has been solved by Quinn, but this is a more simple formal that will result the same answer. If has a couple less formulas in it:

=IF(COUNTIF(B,TRUE)=5,6,IF(COUNTIF(B,TRUE)>0,COUNTIF(B,TRUE),−1))

Reply

Answer 7

Kscheif1983 Author

Level 1

8 points

Aug 28, 2015 7:37 PM in response to charles.christian14

Thanks. You both have been very helpful. I'm getting much better at Numbers with the help from you guys on here.

Reply

Answer 8

t quinn

Level 5

7,043 points

Aug 28, 2015 7:54 PM in response to charles.christian14

Hi charles,

I kept thinking I should be able to get that formula a little trimmer. "IF(COUNTIF(B,TRUE)=5,6..." this part makes perfect sense- eliminate one of those countifs anyway. What kept the last IF() in there for me was Kscheif's desire that the -1 result not show up until after a certain date.

quinn

Reply