• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Solution to summing values

S

SouthKaDaaku

New Member
Hi,

I have a set of data pertaining to users on an app.

The day when the user registers is known as Day 0, the next day as Day 1, the subsequent day as Day 2 and so on.
So for example, if a user registered on 1st August it is his Day 0, while August 2nd is his Day 1.

So if I want to find the users on the app on August 3rd, they will include :
New users on August 3rd
(+) New users on August 2nd who came back on August 3rd
(+) New users on August 1st who came back on August 3rd

The data set is a huge one and encompasses months and I would like a formula or method that helps me do this quickly and efficiently.

Sharing a file with sample data and the way data should be presented (with formulas) so that you can understand easily.

Thanks in advance!!
 

Attachments

  • Chandoo Help.xlsx
    11.1 KB · Views: 5
The catch with this type of calculation (like depreciation) is that the numbers are recorded to be looked up by registration date and days of membership but the calculation is based upon the attendance date. This requires an offset to be introduced into the calculation. Using 365, you could have
Code: 
= BYROW(
      LET(registrationDay, attendanceDate - membershipDays,
          IF(attendanceDate>membershipDays,
             INDEX(numbers, registrationDay, 1+membershipDays), 0)),
  LAMBDA(dailyNumbers, SUM(dailyNumbers)))
 

Attachments

  • Chandoo Help (1).xlsx
    12.2 KB · Views: 8
Hi Peter,

Thank you so much for the solution.

I have another doubt. When I tried dragging the formula across the whole range of data I needed to get the values for, it wasn't working. (The actual data encompasses months)

From what I understand, it has got something to do with the membership date and attendance date.

Can you please tell me what I need to change so that I get the values for 90 days ? Also, could you explain how it has to be done so that I pick it up ?

Thank you once again.
 
Hi Peter,

Went through the formula once again and realized you had named the ranges.

I tweaked your method to include the whole data and replicated it and it work like a charm!!

Thank you so much once again.
 
You must log in or register to reply here.
Back
Top