Eve Excel Addin Formulas: Total Market Order Slots and Number of Market Orders Placed

Taking a look at some Excel Eve Addin formulas that are useful.

A common item on my Excel Spreadsheets shows me how many Market Orders a given character has free, which is the difference between Total Market Orders possible less Orders Placed.

In this case, my character has the maximum possible Market Orders available of 305 and currently has 258 Orders on the Market which leaves 47 free market slots.

In all cases $C$2 is the cell with the character id number.

Total Market Order Slots

The formula to show how many Market Order Slots a character has:

=5 + SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Trade")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*4 + SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Retail")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*8 + SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Wholesale")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*16 + SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Tycoon")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*32

Lets break this down:

The formula is working out the skill level in each of the relevant Trade Skills of Trade, Retail, Wholesale and Tycoon.  Each skill level of Trade gives 5 Order slots , Retail gives 8 slots, Wholesale 16, and Tycoon 32, and of course a character starts with 5 slots anyway.

looking at the part of the formula that works out the Trade Skill slots:

SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Trade")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*4

the formula EVEONLINE.CHARACTER_SKILLS($C$2) would list all the skills the character has.

adding the .name method gives the skill name in text form (an important step).

EVEONLINE.CHARACTER_SKILLS($C$2).name

Therefore, introducing a logic function EVEONLINE.CHARACTER_SKILLS($C$2).name = "Trade" will give the list of FALSE with one TRUE for the Trade Skill.  In excel terms, FALSE = 0 and TRUE = 1.

EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level will show the skill level for every skill that the character has trained.

Therefore, multiplying those two parts together will give a list of zeros except for the Trade Skill which will given 1 x Trade Skill.

And so adding that list up gives the Trade Skill achieved.

and multiplying that by 4 gives the Order Slots for the Trade Skill.

Doing the same for Retail, Wholesale and Tycoon to get the total Order Slots a character has.

Number of Market Orders Placed

=COUNTA(EVEONLINE.CHARACTER_ORDERS($C$2))

Lets break this down:

EVEONLINE.CHARACTER_ORDERS($C$2) will give a list of all orders in the form of name, price, amount left, amount original

And so to determine the total number of orders placed we put that into the COUNTA function to count the number of items in the list.