title: Excel formula for hierarchical data analysis Excel 2010
body: <p>I am trying to write an Excel formula that will take data and look at the starting type and then ending type and then depending on the final type will decide on what worksheet, column and row it will go into.</p>
  
  <p>I want to set a priority system so if it is a planet it is top and if it's a moon it comes second and large asteroid would be third and so on. If the planet is the subsequently reclassified as a moon it would still stay in the planet column as the initial classification has a higher priority than the final classification.
  So if an object is identified as a planet then it comes top and does not go into any other columns. If it is a moon it would go into moon column on initial identification but would move to the correct column on final classification.  </p>
  
  <p>So everything would be classified and if at any point something is classified as a planet it will stay stay in the planet column. If something is classified as a moon it will stay in the moon column despite its final reclassification unless it's reclassified as a planet then it will move to the planet column as it is of a higher priority than moon and so on. There are going to be 56 classifications in order of priority. This is what I have come up with:</p>
  
  <pre><code>=IF(OR(Data!B2="Planet", Data!C2="Planet"), 1, 0)
  =IF(OR(Data!B3="planet", Data!C3="moon"), 1, 0)
  </code></pre>