Need help with creating a formula over multiple rows and columns

[Jonesglen]

Hi All,

I am trying to create a formula that spans over multiple rows and columns to work out if a unique ID has graduated into a new level and then assigning a value to a new column from which to count or highlight.

Attached is an example of the spreadsheet, length of booking is worked out using Days formula between start and end date and formatted in a table.

For example ID number 2 never graduated into a new level. However ID 3 graduated into a new level and progressed through the program. Is their a way to assign the number of times graduated by a unique ID. This way I would get a value of 0 for ID 2 but a value of 4 for ID 3. bearing in mind that if their is a change of time for a booking this will create a new row but with the same level as seen in the spreadsheet, there is also regression of level.

If possible is it then then possible to track which levels students move into after completing one level and measuring the drop out rate for levels whose bookings end there.

Also is it possible to create a Days formula between a cease date and start date over two rows of Data but only if there is the same ID so I can measure days between new bookings.

Any help would be much appreciated

[FlameRetired]

Hello Jonesglen and welcome to the forum.

To best describe or illustrate your problem you would be better off attaching a “dummy” Excel workbook
without sensitive data. The workbook should contain the same structure and some dummy data of the
same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this
format abc-123 then that should be reflected in the dummy workbook. Please include samples of the
expected results hand typed if necessary.

To attach a file to your post,
click “Go Advanced” (next to Post Quick Reply),
scroll down until you see “Manage Attachments”,
click that and select “Add Files” (top right corner).
click on “Browse”
select your file(s)
click “Open” click “Upload” click “Done” (bottom right)
click “Submit Reply”
Once the upload is completed the file name will appear at the bottom of the reply editor window.

Looking forward to working on this one.

[gak67]

As FlameRetired said it is easiest for us with a sample sheet. Surely though, if ID 4132 has 0 graduations, ID 4221 has 3 graduations, not 4?

[Jonesglen]

Sorry you are correct 3 graduations