Formula to identify Levels from CEO

[tttt2020]

Hi universe of experts!
Hope you are all well, I'm looking to help identifying levels from the CEO in data set, wondering if I could get some help on a formula I can use or what would be the best way to determine this without going manually. I've attached my sample data

I'm essentially trying to determine values in that currently appear in Column F (Levels from CEO)

CEO being 0 or CEO the top of the hierarchy, then their direct reports should be -1, then the direct reports of the CEO's direct reports would then be -2 and so on. If anyone can help me with this would be amazing!!!!!!!!!

I attached a sample data set with what the final result should look like with the column of levels from CEO populated by formula (column F)

[MarvinP]

Hi tttt2020 and welcome to the forum,

You will need to put a zero in for the CEO and then this formula for all others.

Please Login or Register  to view this content.

See the attached. You can then sort on employee number and all works fine.
Levels from CEO Sample Answer.xlsx

[tttt2020]

Hi tttt2020 and welcome to the forum,

You will need to put a zero in for the CEO and then this formula for all others.

Please Login or Register  to view this content.

See the attached. You can then sort on employee number and all works fine.
Attachment 667136

OMG MARVINP!!!!! YOU ARE AN AMAZING PERSON!!!!! It is exactly what I was looking for and you have literally saved my sanity from crumbling into the sea come report time!!!! I cant thank you enough!!!!

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

Note: As you are new I will add it for you.Please read forum rules which you might have missed

https://www.excelguru.ca/forums/show...s-from-the-CEO