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