Rolling 6 month point formula

[drewszone32]

Hi!
I am looking to find out how to create a formula that will do a rolling 6 month point structure. Basically total point that can be accumulated in 6 months is 7. I want to have it take a point off when the 6 months is up. The point structure will be 1 thru 7 . It can be half points in there as well.

I need to figure out sooner than later.

Can you help? Attached is a basic look at file.

Thank you in advance for your help.

 

[Luke M]

Hi drewszone32
Could you populate you example file with some example numbers? You talk about points 1-7, and taking off points, which makes sense to you I'm sure, but with no other context, mean nothing to us. Is each row a different person? You talked about months, but your dates at top are days, so I'm not sure how that works either.

 

[drewszone32]

I attached a new version. Yes there will be multiple people in file listed in Jane Doe area. It will be a daily upload inputted for previous day. I am trying to figure out if Jane Doe 1 gets a 1 point on Jan 1st need report and July 1st comes along need the point to go away it is past the 6 month of when point was issued. Hope this makes more sense.

 

[Luke M]

AH, much clearer, thanks!
Formula in cell C2:
=MIN(7,SUMIFS(D2:ND2,$D$1:$ND$1,">="&EDATE(TODAY(),-6),$D$1:$ND$1,"<="&TODAY()))
Copy down.

Caculates total points between today and date 6 months ago.
Maximum amount of points you can have is 7.

 

[drewszone32]

Luke, I am attaching another excel file. It doesn't seem like the formula is working. As you can see in total points column people are showing points but in Points sub column with your formula it shows 0. I would like Points Sub column show total points and then when 6 months which equals about 181 days the 0.5 point on 1/1/2014 will fall off and about will now show 35 if in Point sub column if no other points were accumulated in the mean time. I hope I am explaining this correctly. Thank you for all your help with this one.

 

[Luke M]

Ok...now I'm not sure what this phrase in OP meant.

Basically total point that can be accumulated in 6 months is 7.

I also do not understand how if we have 36 pts, and drop 0.5 pts, we now have 35 pts instead of 35.5, as that doesn't make sense mathematically.
Next, this phrase:

if no other points were accumulated in the mean time.

Adds more confusion. Do we always keep a 6 month window, or are there some scenarios where we would look at a longer range??

I see that in Book2, you moved the cells around, but my original formula was still pointing to range who showed at first. This needed to be adjusted. Since it seems there it no longer a 7 pt limit, I'll remove that as well.
Formula in C6:
=SUMIFS(E6:NE6,$E$5:$NE$5,">="&EDATE(TODAY(),-6),$E$5:$NE$5,"<="&TODAY())

Formula adds up points in 36:NE6 where the data is within the last 6 months.

As indicated, I have a lot of confusion over what exactly you are trying to calculate, or how the math is supposed to work.