We help IT Professionals succeed at work.

EE Rewards Calculator In Excel #2

This is a rehash of this question which lost it's way here on EE.
(I've added some sample data to the spreadsheet)
================================================================
I figured out how to get my EE points total for the past 12 months
(It updates automatically every month).

Based on the 50,000 points in the last 12 months EE rewards rule, I can manually work out
that I have enough points to retain free premium membership until
the end of December 2018 (If I get no more points)?

How can the end of rewards month be automatically calculated Excel?
EE.xlsx
Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I've inserted a "points since" column as C with =SUM(B3:B$39)
and in column D: = IF(AND(C3 > 50, C4 < 50), A3+364,) formatted as MMM DD;; (to suppress zero dates).
That now displays "Dec 18" at Jan 18 (row 14 column D), and nothing in the other rows.
Roy CoxGroup Finance Manager

Commented:
Do you mean something like

=SUM(H18:OFFSET(H18,-12,0))
EirmanChief Operations Manager

Author

Commented:
Hi Qlemo - I can't figure how to integrate your formulae into my sheet.
Surely column as C with =SUM(B3:B$39) would include months more than a year old.

Could you post the file?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
The important column is D, not C. C is just a running sum for "total points starting with month X".
Q_29100325.PNG
EirmanChief Operations Manager

Author

Commented:
Do you mean something like =SUM(H18:OFFSET(H18,-12,0))
I think that only adds up my points Roy (which I have already done).

I want to calculate when Premium Rewards expire
Based on the 50,000 points in the last 12 months EE rewards rule
Group Finance Manager
Commented:
I've used Conditional formatting to highlight months in Column H when the preceding 12 months match or exceed the 50000 criteria
EE--1-.xlsx
EirmanChief Operations Manager

Author

Commented:
That works Qlemo! Thank you.

I'll give others a chance to post their solutions and close this on Monday evening.
EirmanChief Operations Manager

Author

Commented:
Interesting methodology Roy - Thanks
(I'll have to study the OFSET Function).

I calculated AUGUST as the end month and your calculation indicates September.
I'm also getting a lot of #REF errors
Roy CoxGroup Finance Manager

Commented:
The REF errors are because the formula range is not there. I didn't worry about them because I would hide that column or use IFERROR
EirmanChief Operations Manager

Author

Commented:
Thanks Roy - It certainly looks like the more elegant solution.
Roy CoxGroup Finance Manager

Commented:
You could probably add a column to show how many points required to maintain the free 50K
Most Valuable Expert 2013
Commented:
Thanks for linking this, I think Roy has already solved this beautifully.

Programmatically I was looking at:

(Hopefully this will work but typing on tiny phone screen!)

—MonthNow = Current Month
—(MonthNow -/+ N) = a month N months before/afterCurrent Month

Here we go ...

FiftyK = 0
N = 11
MonthNow >= 3000 = Next Month is FREE !
Let’s start a loop N times
If N<1 go to :Fail
FiftyK = FiftyK + (MonthNow - N)
Does FiftyK >= 50000 ? Yep go to : Freebie , Nope > carry on :(
If you get here it’s time to start again N decreases by 1  & back to top of the loop - no freebies yet.
:Fail - End of loop bad luck if you get here, no free stuff for you
:END
:Freebie
Whoop, you did it! You get free stuff at least until ;
(MonthNow + N)

I guess this will either need vba or a very long nested IF

Roy?
EirmanChief Operations Manager

Author

Commented:
I'll close this by Tuesday.
Most Valuable Expert 2013
Commented:
EirmanChief Operations Manager

Author

Commented:
Thanks, Gracias, Merci, Grazie, ありがとう, do jeh, Danke sehr, Khop Khun Mak Kha, Spasiba, 감사합니다, Takk,  Mahalo, Toda, Efharisto
Roy CoxGroup Finance Manager

Commented:
Pleased to help