=IF formula with nested functions

%3CLINGO-SUB%20id%3D%22lingo-sub-1452351%22%20slang%3D%22en-US%22%3E%3DIF%20formula%20with%20nested%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1452351%22%20slang%3D%22en-US%22%3E%3CP%3EHere's%20the%20situation%3A%20a%20participant%20can%20earn%2025%20points%20each%20day.%26nbsp%3B%20If%20the%20participant%20earns%20the%20maximum%2025%20points%20each%20day%20for%206%20days%20in%20a%20row%2C%20the%207th%20day%20is%20double%20points.%26nbsp%3B%20The%20streak%20resets%20the%20same%20day%20the%202x%20points%20are%20earned%20or%20when%20less%20than%2025%20points%20are%20earned%20in%20a%20day.%26nbsp%3B%20In%20other%20words%2C%20the%20day%20they%20are%20earning%202x%20points%20is%20also%20the%20start%20of%20another%206-day%20streak.%26nbsp%3B%20I%20am%20trying%20to%20write%20a%20spreadsheet%20where%20the%20participant%20needs%20only%20to%20record%20their%20daily%20points%20while%20the%20spreadsheet%20keeps%20track%20of%20where%20they%20are%20in%20the%20streak%20and%20awarding%20when%20earned.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20pretty%20sure%20a%20%3DIF%20formula%20with%20nested%20functions%20is%20the%20way%20to%20go%20but%20I%20am%20getting%20lost.%26nbsp%3B%20I%20can't%20figure%20out%20how%20to%20get%20the%20formula%20to%20reset%20once%20the%20reward%20is%20earned%20and%2For%20when%20the%20condition%20isn't%20met.%26nbsp%3B%20Any%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1452351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Here's the situation: a participant can earn 25 points each day.  If the participant earns the maximum 25 points each day for 6 days in a row, the 7th day is double points.  The streak resets the same day the 2x points are earned or when less than 25 points are earned in a day.  In other words, the day they are earning 2x points is also the start of another 6-day streak.  I am trying to write a spreadsheet where the participant needs only to record their daily points while the spreadsheet keeps track of where they are in the streak and awarding when earned.

 

I'm pretty sure a =IF formula with nested functions is the way to go but I am getting lost.  I can't figure out how to get the formula to reset once the reward is earned and/or when the condition isn't met.  Any help would be appreciated.

 

0 Replies