Eve Excel Addin Formulas: Characters connected to the Addin and Character Location

I would have thought that the formula to find out the location of one of my characters would be very easy.

It can be done, it is not easy, it is in the second section two thirds of the way down in this post.  There has to be an easier way of doing this.

But first lets look at the Characters connected to the Addin given this is a key requirement.

Very simple formula

=EVEONLINE.ACTIVE_CHARACTER()

This will list all Characters that are connected to the Addin.

adding the .id method will give all the character IDs, a useful way of seeing all your character IDs.

=EVEONLINE.ACTIVE_CHARACTER().id

and to get a list of where all these characters are currently located firstly get the location id and then use the EVEONLINE.STATION formula to get the station name.

so

=EVEONLINE.ACTIVE_CHARACTER().location_station_id

gives the list of station_ids where each character is located.

And to therefore get what station each is logged into i need to reference each individual cell in the above column using the EVEONLINE.STATION formula.

and so

=EVEONLINE.STATION(G4) where cell G4 is the location_station_id from above, G5 the next one etc

So, how to get a character location in one formula?

It can be done but it seems very complicated.

Assume the character name is on cell D4 (which can be found using the formula EVEONLINE.CHARACTER(A4) where cell A4 is the character ID), then the formula to find the character location is:

=EVEONLINE.STATION(SUM(IFERROR(EVEONLINE.ACTIVE_CHARACTER().location_station_id,0) * (EVEONLINE.ACTIVE_CHARACTER().name=D4.name)))

Lets break this down - three are 3 parts to all this:

EVEONLINE.ACTIVE_CHARACTER().location_station_id

This is as above, it returns a list of the station IDs where each character connected to the Addin is.

Some of these stations, if player owned, will be an error and so throwing an IFERROR around it will return zero if this happens, hence:

IFERROR(EVEONLINE.ACTIVE_CHARACTER().location_station_id,0)

the second part:

EVEONLINE.ACTIVE_CHARACTER().name=D4.name

this compares the name (in plain text) for every character connected to the addin with the name in plain text for the character you are looking it and returns a list of TRUE and FALSE, where only TRUE is for the character you are looking at. (So, this is a list with only one TRUE in it and the rest of the items are FALSE).

In excel terms, TRUE = 1 and FALSE = 0 and therefore multiplying EVEONLINE.ACTIVE_CHARACTER().location_station_id to EVEONLINE.ACTIVE_CHARACTER().name=D4.name will return a list of zeros and one station ID where your character is.

So throwing a SUM around all that adds up the list of zeros and the one Station ID to give the Station ID.

So the final part is to use the EVEONLINE.STATION formula to get the station your character is logged into.

That was complicated!

Footnote

And it is weird.  the range of methods that can be used on the EVEONLINE.ACTIVE_CHARACTER() formula are different to the range of methods that can be used on the EVEONLINE.CHARACTER() formula.  Thats what makes it all complicated.  The EVEONLINE.CHARACTER() formula does not give the option to find the station location of the character.