We help IT Professionals succeed at work.

# EE Rewards Calculator In Excel #2

on
This is a rehash of this question which lost it's way here on EE.
================================================================
I figured out how to get my EE points total for the past 12 months

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

## View Solutions Only

"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.
Group Finance Manager

Commented:
Do you mean something like

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

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?
"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".
Chief Operations Manager

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
Chief Operations Manager

Commented:
That works Qlemo! Thank you.

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

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
Group 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
Chief Operations Manager

Commented:
Thanks Roy - It certainly looks like the more elegant solution.
Group 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?
Chief Operations Manager

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

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

Commented: