Calculating whether a person has upgraded between lists

[stevencampbellcarter]

Hello,

I am trying to write a formula that isn't a gigantic IF statement to calculate whether a person has upgraded their type of house or not within a time period.

The below are the names of the types of housing. I have two of these lists, the first is from the beginning of the time period and the second is from the end of the time period:

Pucca - Normal
Kuchha - Strong
Pucca - IAY
Kuchha - Medium
Kuchha - Weak

The best option on this list is Pucca - Normal and the worst is Kuchha - Weak.

A person can start at any option on this list and end on any option. I only want to measure whether their type of house has been upgraded (i.e. from Kuchha - Weak to Kuchha - Medium). I do not need to calculate whether they have stayed the same or been downgraded.

If the person has upgraded I want to display the value of 1. If they have stayed the same or downgraded I do not want to display anything.

An added complication is that if they have upgraded to Pucca - IAY I want to also do not want to show anything.

I know this might sound a little weird but can anyone help?

Thanks

Steve

 

[aminex]

If I understand correctly, your main problem is how to make for example, Strong > Medium ?
If so, just have another column with a new coding

Pucca - Normal - 3
Kuchha - Strong - 4
Pucca - IAY - 0
Kuchha - Medium - 2
Kuchha - Weak - 1

Then It should be straight forward from there.