Formula to determine user conversion or loss

1. 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  Register To Reply

2. Re: Formula to determine user conversion or loss

No Attachment.  Register To Reply

3. 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.  Register To Reply

4. 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.  Register To Reply

5. Re: Formula to determine user conversion or loss

Hi modytrane. Sorry, it seems i've missed it   Register To Reply

6. Re: Formula to determine user conversion or loss

Am I being ignored?   Register To Reply

7. Re: Formula to determine user conversion or loss Originally Posted by AliGW 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?  Register To Reply

8. Re: Formula to determine user conversion or loss Originally Posted by AliGW 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!  Register To Reply

9. 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?  Register To Reply

10. 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.  Register To Reply

11. 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.  Register To Reply

12. Re: Formula to determine user conversion or loss Originally Posted by AliGW 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.  Register To Reply

13. 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.  Register To Reply

14. Re: Formula to determine user conversion or loss Originally Posted by modytrane 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.  Register To Reply

15. Re: Formula to determine user conversion or loss Originally Posted by AliGW 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  Register To Reply