Splinterlands Social Media Challenge: Using Sheets Formula To Maximise Rewards In Each League

During the past year I have been developing the deck of cards available to me within Splinterlands using fairly limited funds generated through HIVE.

I now have collected 42,000 power which puts me just shy of the Gold rating of 50K.

Now that I have collected almost all of the missing cards in my Chaos legion deck, I've begun upgrading where I can to maximise my rewards. To this end, I have been pushing into Diamond at the end of the season for the past two seasons and it has been relatively successful so far. But my primary focus is on maximising my rewards from each victory to regularly earn better rewards so I can continue to reinvest them.

My first step was collecting Gold Foil Common cards which has been very helpful in raising the RP gained at all levels so far.

Reaping The Benefits

Here are two good examples of the benefit that Gold Foil Commons have at Silver: Battle Link

The Demented Shark is at Level 1 and all other cards meet the Silver Full Earning Status in this lineup. The additional 40% from Gold is really helpful, the small win streak is a nice bonus at 15% and the additional Sneak Focus helped with 30%.

Here is another good example of the Gold Foils in action:
Silver Battle Link

The 50% Gold bonus was excellent here and the recently upgraded Cursed Windeku (Level 2).

The Project

While I am sure there is a perfect fit tool for everything I have created below readily available, this is more an exercise in developing a Sheets formula project for Splinterlands that I can learn from.

I am sure many people on HIVE know plenty more than me about what I am doing so I would always appreciate suggestions that would tidy the project up going forward or increase its functionality.

Tracking the collection

To help me keep on top of all this, I have been creating a tracker which highlights the cards I should upgrade. Whilst I am yet to add in additional features to it, it helps me to identify where my limited funds should be pointed toward next.

Immediately, I noticed that my rare Chaos cards were really obviously under levelled. So I utilised some funds I had available within HIVE and purchased the missing BCX for all of my Rare cards, taking them to Full Earning Status in Silver. I then updated the levels of the cards which brings them to the current state.

How does it work?

You feed the sheet the card name(Merdaali Guardian), element (WATER) and rarity (RARE) and current level (2) and the sheet automatically calculates if your selected card needs an upgrade or not at the League selected in the formula. It makes the calculation by comparing your current card level against the rarity card level for Full Earning Status. If we subtract the Current Level from the Full Earning Status Level then ideally we receive a number that zero or less. The calculation then uses the outcome to select "UPGRADE" or "OK".

• This is a feature I want to expand on later in the project.
• The Element of the card is there purely for filter purposes down the line. It made sense to just add them in at the point of data entry rather than add later.

The tracker will then identify an action to take for that card. So with a Level 2, it will show as 'OK' in the Silver column but 'upgrade' in the Gold column. The Demented Shark demonstrated in the above battle would be 1 BCX and at Silver level it would be highlighted as a card requiring an upgrade

• I will likely build in an Edition option and set the formula to ignore specific editions such as Promos or Untamed for example.

Adding in Gold Foils

'Diemonshark GF' changes colour based on conditional formatting which looks for 'GF' somewhere in the cell - I will be taking this and hopefully turning this into a Tick box option with similar functionality but that is on the list for later.

Identifying Urgent Upgrades

Using this system, I selected Silver as my minimum upgrade level. So in the Silver columns, I added formula using an IF THEN condition which checks the rarity of the card and if it matches Common then Level 3 would be required in Silver, Rare 2 , Epic 2 and Legendary 1.

Using this, I then took the next cell and subtracted the required level from the owned level and checked to see if the answer was greater than 0. If the formula returns a false then it prints "UPGRADE" into the cell. If the response was equal to 0 then it it prints "OK".

Through this formula, I then set up a conditional format in the column for Silver "Upgrade or ok" which reformats the cell with a red fill and a white bold lettering for "UPGRADE". This condition only applies in the Silver section of the Sheet. I'd like to develop this based on tick box scenarios. When the Gold box is ticked it will judge against Gold criteria and target there for highlighted upgrades for example.

Conclusions

Currently, this tracker has helped me to identify both Rare and Epic card level issues and has resulted in a good boost to my RP. It has taken a long time to input the cards manually for now but I'll probably find a quicker way to input them at some point.

What did I learn?

• Creating IF THEN statements in formula and how to stack them
• Rare cards cost me RP in Silver. Upgraded all to Level 2 to gain Full Earning Status in Silver.
• Epic cards are holding my RP back in Silver I am targeting the most used EPICs next due to costs but in most cases it is around a 7% decrease unless Untamed cards are used in the same line up

Most used are:
Uraeus
Magi of Chaos
Prismologist
Forgotten One
Nerissa Tridawn
Magi Necrosi
Djinn Chwala
Revealer
Lava Launcher

With a few of these at level 2 already, I have seen some good benefits to my daily RP totals.

Next Steps?

• I'd like to build in a missing BCX calculator to help me identify how many more I need but I need to figure that out at the moment. I have an idea for it.
• Turn the Gold Foil into a Tick Box if possible but will need to figure that out first.
• Add a desired League selector. I want to play in Gold... tick this box or I want to see what I need for Diamond, just tick this box... possible?
• build in an Edition option and set the formula to ignore specific editions such as Promos or Untamed for example
• Sell the few Untamed cards I have?

Join Splinterlands:

https://splinterlands.com?ref=holdeck

---

Join Splex

...and rent out your cards for some juicy DEC rental fees. Save yourself the hassle of listing and relisting and let the Rental Golem take over for you:
https://splex.gg/?ref=3s5haoqkjteadpf8

How to invest without FIAT in a bear market? : https://peakd.com/hive-167922/@holdeck/how-i-am-investing-for-after-the-bear-market

Setting and redefining goals in a bear market: https://peakd.com/hive-13323/@holdeck/setting-and-redefining-goals-in-a-bear-market

Splinterlands Fire divider by freeztag

All card, icons, Splinter images and stats courtesy of Splinterlands

Ability and Ruleset blurbs taken from Splintercards

Splinter Element Icons taken from Splinterlands
I have reformatted them into PNG files which you are welcome to use here