I am attempting to automate a calculation in excel which is at present eluding me.
I have a large number of historical societies that were formed in a particular year and ceased in another and I am attempting to number their priority at the time they were formed, based on whether those previously formed, have ceased or not.
A typical example is :
The very first society (Blackamores) was formed in 1751 but ceased in 1758. This is therefore No. 1.
The next society (Union) was formed in 1757 which is before (Blackamores) ceased and it did not cease until 1814, so it becomes the 2nd active society at the time.
The next society (Newstead) was formed in 1763 which is after (Blackamores) but before (Union) ceased. It is still in existence in 2015, so it becomes the 2nd active society at the time.
The next society (Warren) was formed in 1768 which is after (Blackamores) ceased and before both (Union) and (Newstead) ceased. It ceased in 1775 so it becomes the 3rd active society at the time.
The next society (Mansfield was formed in 1790 which is after (Blackamores) and (Warren) ceased but before (Union) and (Newstead) ceased so it becomes the 3rd active society at the time.
I have attached a screen shot of a fraction of the list.
WSorry I have been away from my comuter for several days. I believe you can get to your results with a countifs formula, and using different criteria to count the societies that fit within your paramaters, something like this.
this is just an example of the type of formula I believe you will need. As you see it is using only two criteria and you will perhaps need three or more.
Hope this helps.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here