+ Reply to Thread
Results 1 to 15 of 15

Formula to determine user conversion or loss

  1. #1
    Registered User
    Join Date
    01-18-2019
    Location
    Milton Keynes, England
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Formula to determine user conversion or loss

    Hi guys,


    Working on a user conversion topic at the moment and trying to come up with a formula to determine if a user didn't use our product in the past and is using it now can be classed as a CONVERSION and if he has used it in the past and doesn't anymore to be classed as a LOSS - attached is a spreadsheet for example ,where my product is ABC.

    Couldn't think of a single formula rather than using lots of additional columns to help me achieve this.

    Any ideas?

    Appreciate any help.

    Ionut
    User conversion_loss example.xlsx
    Last edited by pandeion; 08-14-2019 at 08:10 AM.

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,496

    Re: Formula to determine user conversion or loss

    No Attachment.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,130

    Re: Formula to determine user conversion or loss

    You never replied to the response you received to your first ever thread here - we do expect you to do so out of courtesy to the respondent, even if their reply did not help. Please revisit that thread and do so. Thanks.

    I will look at your attachment.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,130

    Re: Formula to determine user conversion or loss

    OK - I've had a look. The problems are:

    1. You have provided no sample answers on the column where you want the result - please add about 20 lines - ,anually calculated.
    2. What are the criteria for a conversion? It is not apparent to me from the workbook provided.

  5. #5
    Registered User
    Join Date
    01-18-2019
    Location
    Milton Keynes, England
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Formula to determine user conversion or loss

    Hi modytrane. Sorry, it seems i've missed it

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,130

    Re: Formula to determine user conversion or loss

    Am I being ignored?

  7. #7
    Registered User
    Join Date
    01-18-2019
    Location
    Milton Keynes, England
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Formula to determine user conversion or loss

    Quote Originally Posted by AliGW View Post
    OK - I've had a look. The problems are:

    1. You have provided no sample answers on the column where you want the result - please add about 20 lines - ,anually calculated.
    2. What are the criteria for a conversion? It is not apparent to me from the workbook provided.
    Thank you for looking into this.

    1. The column for the result should only say "Conversion" or "Loss"
    2. The criteria for a conversion is: if the same user has been using a different product at a previous date and now (given by the most recent date) is using ABC - it can be classed as a Conversion. And the other way around, if he was using ABC previously and not anymore -> to be classed as a Loss.
    * The column with the date, can be used to determine first, last, previous Product usage.

    Hope the above makes sense?

  8. #8
    Registered User
    Join Date
    01-18-2019
    Location
    Milton Keynes, England
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Formula to determine user conversion or loss

    Quote Originally Posted by AliGW View Post
    You never replied to the response you received to your first ever thread here - we do expect you to do so out of courtesy to the respondent, even if their reply did not help. Please revisit that thread and do so. Thanks.

    I will look at your attachment.
    I have now - apologies for the oversight!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,130

    Re: Formula to determine user conversion or loss

    Why is the data not in chronological order? Is that correct? Is the real data out of order?

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,130

    Re: Formula to determine user conversion or loss

    This won't give everything you want, but give it a go:

    =IF(G2="Y",IF(LOOKUP(2,1/(($A$2:$A$40=A2)*($E$2:$E$40=MINIFS($E$2:$E$40,$A$2:$A$40,A2))),$F$2:$F$40)<>"ABC","Conversion","New"),"")

    I really need to see manually calculated outcomes in your sample workbook to go any further.

  11. #11
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,496

    Re: Formula to determine user conversion or loss

    Look at the attached file.
    I've added a button to sort the data so names are in alphabetical order and dates are chronological.

    It helps to make the formula simple and easy to follow the logic.

    I've added this formula to column H.

    HTML Code: 
    =IF(OR($A3=$A2,$A1<>$A2),"",IF($G2="N",IF($G1="Y","Loss",""),IF($G1="N","Conversion","")))
    Here's the logic used.

    IF a name is first and last [used only once] then result is blank [doesn't matter what they use because it's not a Loss or Conversion].
    If the name is not last in a list, again it doesn't matter because we only have to look at usage for last occurance.
    If the name is last, we compare previous usage with current and determine if it's a Loss or Conversion.

    If this doesn't match you expectations, please provide a detailed logic with different scenarios.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-18-2019
    Location
    Milton Keynes, England
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Formula to determine user conversion or loss

    Quote Originally Posted by AliGW View Post
    This won't give everything you want, but give it a go:

    =IF(G2="Y",IF(LOOKUP(2,1/(($A$2:$A$40=A2)*($E$2:$E$40=MINIFS($E$2:$E$40,$A$2:$A$40,A2))),$F$2:$F$40)<>"ABC","Conversion","New"),"")

    I really need to see manually calculated outcomes in your sample workbook to go any further.
    Hi AliGW and many thanks for this. I've managed to sort it out.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,130

    Re: Formula to determine user conversion or loss

    Glad it got you started. How did you resolve it? We ask that you say so for the benefit of others reading this thread in future.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  14. #14
    Registered User
    Join Date
    01-18-2019
    Location
    Milton Keynes, England
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Formula to determine user conversion or loss

    Quote Originally Posted by modytrane View Post
    Look at the attached file.
    I've added a button to sort the data so names are in alphabetical order and dates are chronological.

    It helps to make the formula simple and easy to follow the logic.

    I've added this formula to column H.

    HTML Code: 
    =IF(OR($A3=$A2,$A1<>$A2),"",IF($G2="N",IF($G1="Y","Loss",""),IF($G1="N","Conversion","")))
    Here's the logic used.

    IF a name is first and last [used only once] then result is blank [doesn't matter what they use because it's not a Loss or Conversion].
    If the name is not last in a list, again it doesn't matter because we only have to look at usage for last occurance.
    If the name is last, we compare previous usage with current and determine if it's a Loss or Conversion.

    If this doesn't match you expectations, please provide a detailed logic with different scenarios.
    Hi modytrane. Thanks for this - I've managed to use your solution. Had to only add that if it's a new name and using our product consider it a Conversion (even if technically it isn't). Many thanks once again.

  15. #15
    Registered User
    Join Date
    01-18-2019
    Location
    Milton Keynes, England
    MS-Off Ver
    365 ProPlus
    Posts
    9

    Re: Formula to determine user conversion or loss

    Quote Originally Posted by AliGW View Post
    Glad it got you started. How did you resolve it? We ask that you say so for the benefit of others reading this thread in future.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Used modytrane's above logic and added an extra simple bit to consider new users using our product as converted.

    Attachment 637481

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1