Analyse Heroes ala RedPython


Atlantis heroes inside :slight_smile: I wish you many good pulls!


Thanks for this @RedPython however I have a problem

I have taken a copy into my MyDrive and it seems to work fine. However a work a lot on my own Excel files offline often while travelling and when I download the file and open it in Excel 2016 I get lots of errors.

This appears to be because you use a function that doesn’t seem to exist in my version of Excel and that is switch

Any ideas as to how to get round the problem?


This is native google drive sheet file, not import from Excel, that means it’s not about different versions, it’s completely different software. I didn’t create it to be compatible with various tools, since I’m primarily using google sheets in my life and various tables here are also just google sheets. They do work offline as well, if you use google chrome and probably some extension, can’t recall, it just asked me something when I installed the machine and it’s just working later on.

switch is just simplified version of many nested ifs, if that helps?

1 Like

temporarily down, will be back soon


And is back :slight_smile:
Thanks everyone for being patient :slight_smile:

1 Like

Thanks for the update and cleanup!

Just FYI, I used the hero count and notes to better manage my roster; in my copy I’m adding the count back in and using ascension plan column like I used the notes. (I never used the ascension plan, and I figure one column for notes/status is plenty.)

What I did most with the count was put in a count of zero for heroes on my wish list … or maxed versions of heroes I’m currently ignoring, and made some filter views that showed my roster excluding them…and others that let me see how they would compare to the heroes I have.


ok, I’ll put back count column in next version

One reason for removing both was to shrink fixed left side, and I think ascending part is useful for someone, I put it there because I used it to figure out what materials I have/need and so on. currently I’m not using anything, I just look the roster and see what I need to do next, it probably all transfered in my head :rofl:


Thank you for sharing your hero analysis spread. Very cool!
It appears that your true level calculation is erroneous and wanted to point it out or get confirmation it is working as intended. Specifically the vlookups appear to point consistently 1 column to the left of where they need to. For example, if we input the tier as 2 it goes to the column for tier 1.

Thanks again for sharing. I know that took some work

1 Like

Are you sure?

I never noticed that, eg 3 star hero tier 2 level 2 is level 32, and it showed correctly. It could happen that some moving around messed something up though.

Just to be on the safe side, let me point out that you need to write both information, so tier and level, it does not assume maxed in some tier.

So 3.60 is maxed 4 star in tier 3, and you need to write both 4 for star, 3 for tier and 60 for level and true level should show 150.

I can’t recall what it does if there’s no level, only tier, I think it just see 0 there and sums like that.

Anyway, if error is still here after you check this, can you please give me screenshot of data and wrong result and sheet name where error occurs so that I can check? Thanks :slight_smile:

1 Like

One thing currently lacking is emblem tracking of the heroes. Now this might add quite a lot of complexity to track this in detail, but perhaps it’s possible to only track the 4 major ones (atk, def, hp, special) and only track the count of each.

The emblems which modifies the base stats add up to quite a difference in damage, tankyness etc. Knowing your special % is less important but I could see it as a nice to have.


I am pretty sure
The following formula is in the True Level Column (col G) on the My Heroes tab


Column B (My Heroes tab) = Tier
Column E (my heroes tab) = # of Stars
Column K (input tab) = 1st column of lookup range (# stars)
Column L (input tab) = 2nd column of lookup range (tier 1)
Column M (input tab) = 3rd column of lookup range (tier 2)

S0…the way I read this is:

Look at B4, if it is 2 then look at the # stars (e.g. 5)
…based on # stars go to the InputTrueValue table and look up the associated value from the second column into the array (which is the tier 1 column and not the tier 2 column)

This is how a vlookup works on excel 2016 that I am using at home. If this online version acts differently then my argument is not valid.

My assumption is that the named array “inputTrueValue” is input tab K12:Q16

Again…thank you for sharing this work