Mk. Ultra Guild Management Sheet – Page 3 – General

Xcellers · 2019-08-30T06:21:24Z

Mk. Ultra - Comprehensive Guild Management Spreadsheet by Xcellers • 26th August 2019 A key challenge for Classic raiding guilds is heavy administrative tasks, involved in managing one or more 40-man raid groups. Adding to this challenge, is the strategic overview needed to successfully ensure the fair and transparent distribution of loot in Loot Council based settings. To this end the Mk. Ultra spreadsheet seeks to provide a intuitive & comprehensive platform for this, easing the administrative burden on officers, while ultimately benefiting the guilds member base. NB. Guide Materials and Public Version is currently outdated, and as there is a big push for the next major content update before ZG launches, it will remain outdated for the time being. The current version changes are covered in the newsletter that went out to all users and is linked in the discussion - for reference you can find the info here:Important Notice - July 2020 - Due to IRL obligations, then the major 2.0 content overhaul is unlikely to be launched in advance of AQ, additionally, expect extended delays in getting access to the sheet in the coming months. I will be deploying to a diplomatic mission in Africa for the next foreseeable future and as you might expect, my focus is on that. I am still hoping to manage everything, but fair warning given! images/guides/rg_contents.png IntroductionSpreadsheet OverviewBasicsPublic Test VersionUsageUpdates Version 1.2 Version 1.3 Version 1.4 Version 1.5 Version 1.6 Version 1.7 HOW-TOs Adjusting Ranks Recovering Class Icons Adding Week Numbers to Raids/Attendance Adding profession specialisations Switching to Simple Attendance Tracking instead of Raid Builder mode Adjusting Time Formats Cleaning up Unused Tabs Hotfix the Compare Tab Enable Scripts Add room for duplicate items FAQCredits intro /images/guides/wp_intro.png The Mk. Ultra Guild Management Spreadsheet consists of a range of core elements, tracking valuable information on guild composition, crafting capabilities and heirachy to facilitate a single-visit location for all daily information needs. As noted, the spreadsheet maintains a design focus onloot council based guilds, as these adhere to less rigid formats for loot management, than the strict point accounting systems used in DKP settings. The spreadsheet is specifically designed as a non-profit to non-profit, community oriented project, and as such the target audience is solely guilds operating without any economic interests tied to the game. This stance means that all users will be screened, providing practical information to facilitate the correct version of the sheet, opt in/out for updates and general administrative information. For more information on getting access to this guild management tool for your guild, please see the usage section. As as passion-project, the Mk. Ultra is intended for running updates, as the current configuration of the the spreadsheet requires users to copy a faction specific sheet to their own google drive, and populate/implement it from there, it also means that updates has been decentralized. This means, that guilds who opt in for updates, will receive occasional update information with clear guidelines on updating the sheets. The first waves of these updates has already been successfully deployed and a number of requests are already being converted into new, fresh updates for the sheet. For those wishing to avoid this kind of hassle, then the initial invite version you will receive, will always be updated to the newest version, and each release can be tracked via. the guide tab. section1 /images/guides/wp_overview.png Below is a run-through of how the sheet is currently configured, modules are still being released so this overview may be expanded in the future with commonly requested features such as attendance tracking, raid composition and similar. At its core, the spreadsheet will share data between tabs, basing itself around the initial Roster tab. The populated Roster tab will provide player data for all drop-down menus, and as a basis for all calculations across the sheet. The goal is to automate as many administrative tasks as possible, while still keeping the day-to-day operations as transparent as possible for members. It is highly recommended to protect most of the available sheets so that only guild officers have direct access to them, while still leaving them view-able by members, to ensure better oversight; notably in regard to loot distribution. Roster Data Guild Info Master Crafters Loot Distribution Compare Signups Raid Composition Attendance MC Assignments MC BWL AQ40 Naxx WBs Ony ZG AQ20 Guide The Roster is intended to be filled out by regular members in the following segments "Basic-, Class-, Crafting-, PVP-, Attunement- and Misc Info". The information can be sorted in whatever ways wanted, but by class gives the best overview. As soon as a member selects a class, the Roster will automatically assign them a corresponding colo...

Dumbelmort

(@dumbelmort)

Posts: 2

New Member

Hey,

We've been using the sheet for a few weeks and we find it very useful.

We added a few modifications beacause of your luck : we added rows for wrist/waist loots.

I don't know if it's possible, but we would find useful to have a "Loot review" tab ; a tab where we could select a player in the Roster and the function will search in the MC/BWL/etc.. tab to return the list of the loot/date per player. That will be easier to comprae 2-3 player tto know to who give a loot.

Great job for the Sheet !

Dumbel

Posted : 20/10/2019 11:33 am

chumppi

(@chumppi)

Posts: 1

New Member

Our guild has been using this for a couple of weeks now. Big sigh of relief not to having to rely on random personal sheets and scattered information anymore! All formulas are in a clear and understandable and makes it easy to make adjustments with little Sheets experience.

Thank you!

Posted : 21/10/2019 9:38 am

Xcellers

(@xcellers)

Posts: 141

Estimable Member

Topic starter

Absolutely amazing spreadsheet.

Thank you so much Xcellers
I have only one suggestion regarding the loot distribution. It would be a very good thing to have an overview sheet with all members listed as well as the loot they received and which date.

Something like this:

Once again, a huge thanks for the sheet which will be incredibly useful for a long time.

Hey,

We've been using the sheet for a few weeks and we find it very useful.
We added a few modifications beacause of your luck : we added rows for wrist/waist loots.

I don't know if it's possible, but we would find useful to have a "Loot review" tab ; a tab where we could select a player in the Roster and the function will search in the MC/BWL/etc.. tab to return the list of the loot/date per player. That will be easier to comprae 2-3 player tto know to who give a loot.

Great job for the Sheet !

Dumbel

Our guild has been using this for a couple of weeks now. Big sigh of relief not to having to rely on random personal sheets and scattered information anymore! All formulas are in a clear and understandable and makes it easy to make adjustments with little Sheets experience.

Thank you!

Thank you for all the positive feedback guys, and for the suggestions - initially the idea sounds great, tho also rather complex, so its not something I will be able to fit into the next update (if its reasonably possible to include into the sheet naturally)

Posted : 22/10/2019 2:19 pm

Xcellers

(@xcellers)

Posts: 141

Estimable Member

Topic starter

The Mk. Ultra has just gone live with version 1.6, adding a number of features to improve LC decision making, better analytics and a number of quality of life upgrades that should ease day to day operations in guilds using the sheet!

The main post has been updated with all the new info, a few new how-to guides and a fully updated public test version

Enjoy!

Posted : 25/10/2019 6:37 am

Schmoopy

(@schmoopy)

Posts: 3

New Member

Cheers for the update :D

Loving the new compare sheet.

Sadly the attendance problem still persists and shows the wrong percentage value. My guildy had a look and found a band aid fix to the problem.

=IFERROR(G3/(countifs(Attendance!$3:$3,">="&Roster!T3) +1),0)

That SUM fixed our issue for everyone, apparently the original SUM was 1 value off for some reason, felt like a google docs bug - If the percentage is off (over 100%) it was an attendance or join date error then.

Before:
https://imgur.com/a/wMWNSQi

After:
https://imgur.com/a/i9H1a9N

Thanks again and keep up the great work.

Posted : 25/10/2019 5:01 pm

Xcellers

(@xcellers)

Posts: 141

Estimable Member

Topic starter

Cheers for the update :D

Loving the new compare sheet.

Sadly the attendance problem still persists and shows the wrong percentage value. My guildy had a look and found a band aid fix to the problem.

=IFERROR(G3/(countifs(Attendance!$3:$3,">="&Roster!T3) +1),0)

That SUM fixed our issue for everyone, apparently the original SUM was 1 value off for some reason, felt like a google docs bug - If the percentage is off (over 100%) it was an attendance or join date error then.

Before:
https://imgur.com/a/wMWNSQi

After:
https://imgur.com/a/i9H1a9N

Thanks again and keep up the great work.

Hey man,

So I tried to replicating the error in the live sheets and in my test version, and the % values in attendance comes out perfectly so I really dont know what to say. What I can say is there has been a shitload of sheets related bugs during the development process with ghost values popping up left and right, so I am in no way doubting that you have this error - I just cant explain it :D

At current levels, the sheet is 50% coding, 40% techno magic and 10% wishful thinking :lol:

That being said, then I am closely watching all the feedback coming in, so if others have the same problem, then i will share your solution with them!

Posted : 26/10/2019 6:09 am

Schmoopy

(@schmoopy)

Posts: 3

New Member

Cheers for the update :D

Loving the new compare sheet.

Sadly the attendance problem still persists and shows the wrong percentage value. My guildy had a look and found a band aid fix to the problem.

=IFERROR(G3/(countifs(Attendance!$3:$3,">="&Roster!T3) +1),0)

That SUM fixed our issue for everyone, apparently the original SUM was 1 value off for some reason, felt like a google docs bug - If the percentage is off (over 100%) it was an attendance or join date error then.

Before:
https://imgur.com/a/wMWNSQi

After:
https://imgur.com/a/i9H1a9N

Thanks again and keep up the great work.

Hey man,

So I tried to replicating the error in the live sheets and in my test version, and the % values in attendance comes out perfectly so I really dont know what to say. What I can say is there has been a shitload of sheets related bugs during the development process with ghost values popping up left and right, so I am in no way doubting that you have this error - I just cant explain it :D

At current levels, the sheet is 50% coding, 40% techno magic and 10% wishful thinking :lol:

That being said, then I am closely watching all the feedback coming in, so if others have the same problem, then i will share your solution with them!

Hey,

So on the live sheet you linked to copy (sorry ahead of time for inputting the small amount of data that I did) - I added a few extra dates in "Attendance" since they were all the same date and changed the join date from 21st to 20th.

Tried to replicate the problem I had and got this:
https://imgur.com/a/v2Q8n9D

Edit: Changing the join date from 20th -> 19th gave me this result:
https://imgur.com/a/8iC3xNF
So it seems that if you have the join date of the person the same as their first raid, it can throw off the total result by 1.

- This would also make sense why our guilds dates were off by 1 since we use the "join date" as a reference for when the member got promoted to trail. This is to to avoid socials who have been in the guild since creation, to end up having low attendance after their first raid.

Edit 2: Found a quick easy solution:
https://imgur.com/a/DaNhCPy
It seems to bypass the same date issue. (I left the SUM as it was originally on the spreadsheet)

I only adjusted the join dates and the dates on the attendance sheet - nothing that would affect anyone who would copy the sheet -

Hope it helps.

Posted : 26/10/2019 11:20 pm

Xcellers

(@xcellers)

Posts: 141

Estimable Member

Topic starter

Ahh I guess that explains why I couldnt reproduce it - well great spotted, i made the adjustment to the other sheet and I will just write a small How-To notice on it, in-case others get the same error!

Thanks a bunch Schmoopy

Posted : 27/10/2019 2:52 am

Xcellers

(@xcellers)

Posts: 141

Estimable Member

Topic starter

New milestone; guild no. 500 just adopted the Mk. Ultra for their guild management!

Posted : 28/10/2019 3:15 pm

Xcellers

(@xcellers)

Posts: 141

Estimable Member

Topic starter

There might be a few, entirely in-explainable bugs going around with the new compare tool feature, if anyone runs into issues with users not having anything counted on their profiles, then lemme know so i can try to pinpoint the item issue in question!

Posted : 30/10/2019 2:44 am

Qhrynne

(@qhrynne)

Posts: 1

New Member

Me and my guilde are using the Xcellers spreadsheet since 3 weeks and everything is working like it supposed to be! :)

Thank you for that awesome work!

Posted : 05/11/2019 8:21 am

Agatea

(@agatea)

Posts: 1

New Member

This is just incredible!

The greatest boost for our Catalan guild. All our members are so happy and excited for this spreadsheet!
Using it right now. Many thanks for your great work and keep doing! Maybe a website on future? Could get famous!

Posted : 06/11/2019 9:17 am

Xcellers

(@xcellers)

Posts: 141

Estimable Member

Topic starter

This is just incredible!

The greatest boost for our Catalan guild. All our members are so happy and excited for this spreadsheet!
Using it right now. Many thanks for your great work and keep doing! Maybe a website on future? Could get famous!

Me and my guilde are using the Xcellers spreadsheet since 3 weeks and everything is working like it supposed to be! :)

Thank you for that awesome work!

Thanks guys, happy to hear that you and your respective gangs have been enjoying the sheet!

Posted : 07/11/2019 10:55 am

Xcellers

(@xcellers)

Posts: 141

Estimable Member

Topic starter

New milestone; guild no. 600 just adopted the Mk. Ultra for their guild management!

Posted : 08/11/2019 2:20 am

LeStone21

(@lestone21)

Posts: 1

New Member

Just starting to use the spreadsheet now and it's an absolute life saver! So nice to see all the necessary info in one spot. It's easy to use too, thank you Xcellers!

Posted : 08/11/2019 6:03 am