Potion Spreadsheet (Cost ratios etc)

Discussion in 'Guides' started by Inusama, May 10, 2020.

  1. Inusama
    Offline

    Inusama Donator

    Joined:
    Mar 26, 2015
    Messages:
    1,654
    Likes Received:
    5,271
    Gender:
    Male
    Country Flag:
    IGN:
    Inusama
    Level:
    200
    Guild:
    Oblivion

    Click here to open in a new tab!
    Well, someone had to do it. Enjoy a bunch of useless potion information.
    Thanks to @Tim for inspo and @LichWiz for doing the formulas!

    I had fun wandering around to every grocer in the game to check prices. I've included every single HP/MP healing item sold by NPCs.
    Notes:
    • To edit the boxes for % cost ratios, please make a copy of the sheet! (As in, open the sheet. Click File in the top left and then Make a copy.)
    • The default numbers in there are from my 179 archmage
    • The cost ratio for the % potions is gonna be a tad misleading if you have high HP and MP.
    • NPC Mo and NPC Jane won't sell you anything until you complete their quests.
    • Lemme know if it breaks lol
    Pot facts:
    Sorry dark knights, nothing heals more than 5k without being a % pot.
    Ludi is cheapest for most potions
    Mapleade has the worst ratio lmao
     
    Last edited: May 11, 2020
    greenthing, Sen, Cola and 8 others like this.
  2. Tiffaux
    Offline

    Tiffaux Donator

    Joined:
    May 30, 2018
    Messages:
    201
    Likes Received:
    189
    Gender:
    Female
    Location:
    TulipsHQ Ch15
    Country Flag:
    Level:
    12x
    Guild:
    Tulips
    This is actually what I have been waiting for to be made. It's amazing, props!
     
    greenthing and Inusama like this.
  3. Tect
    Offline

    Tect Well-Known Member

    Joined:
    Aug 15, 2017
    Messages:
    3,200
    Likes Received:
    5,091
    Country Flag:
    IGN:
    DTect
    Level:
    200
    Guild:
    Manon
    hai inu i notice u put blue pills as healing 200 mp. they heal 100mp :p
    upload_2020-5-11_13-15-44.png
    upload_2020-5-11_13-13-19.png
     
    Inusama likes this.
  4. Tiffaux
    Offline

    Tiffaux Donator

    Joined:
    May 30, 2018
    Messages:
    201
    Likes Received:
    189
    Gender:
    Female
    Location:
    TulipsHQ Ch15
    Country Flag:
    Level:
    12x
    Guild:
    Tulips
    I've realized that filling out your own hp/mp doesn't change the % pots ratio and that it's calculated on getting x% of 60.000 (30k hp + 30k mp) which makes it look like the %pots are the absolute best, while this is for almost no one the case of course. I thought there's been made a formula for this but I can't find any formula in the copied sheet.
    Anyways, I calculated it myself, still an amazing sheet. Thank you for the effort <3
     
  5. Inusama
    Offline

    Inusama Donator

    Joined:
    Mar 26, 2015
    Messages:
    1,654
    Likes Received:
    5,271
    Gender:
    Male
    Country Flag:
    IGN:
    Inusama
    Level:
    200
    Guild:
    Oblivion
    Aw man, yeah I have no idea what I'm doing when it comes to Google Sheets. I tried protecting the entire sheet except those two boxes so anyone could edit it but every time I share it the sheet comes out view only.
    Yeah, we did notice how the ratio got skewed for those potions but as I said in the op I don't see any good way around it haha. For now I have changed the HP/MP in the top corner to what my archmage has and now none of the % potions have a price ratio above 1!
    The formula stills shows up and works on the original fine.
    Honster:=(B2)*(B87)/D31
    Ginger Ale:=(B2+C2)*(B88)/D32
    Mapleade: =((B2)*B90+(C2)*C90)/D46
    B87/B88/C90 etc are just the percentage written as a decimal. Like 0.6 for honster. Made the text white so they were hidden.

    Fixed!
     
    Last edited: May 11, 2020
  6. Tiffaux
    Offline

    Tiffaux Donator

    Joined:
    May 30, 2018
    Messages:
    201
    Likes Received:
    189
    Gender:
    Female
    Location:
    TulipsHQ Ch15
    Country Flag:
    Level:
    12x
    Guild:
    Tulips
    I see. The formula's are not in it when you copy the spreadsheet above and paste it into Google Sheets. Idk how you can make it so that people can only adjust the two hp/mp cells. Maybe @Shiyui can help you out. She made the HP-washing calculator (this one)

    Ginger ale formula should be *B89 (.75) instead of B88 (unless something went off due to copy-pasting, but it should be .75). Just pointing it out to make sure you haven't made a mistake in your original file.

    Also I have no clue how to work around the % pots for both hp and mp. I guess (if you can make the formula's work for everyone) ppl can just fill out 0 in either hp or mp, to check how viable a particular % pot is, like (power) elixer. I can't come up with any other solution, but this should do the trick.

    I hope you can make it work publicly including the formula's, would be epic :)
     
  7. Inusama
    Offline

    Inusama Donator

    Joined:
    Mar 26, 2015
    Messages:
    1,654
    Likes Received:
    5,271
    Gender:
    Male
    Country Flag:
    IGN:
    Inusama
    Level:
    200
    Guild:
    Oblivion
    Do the formulas work if you open the sheet and make a copy via google sheets? I’ve added a link under the embedded sheet ;)
     
  8. Tiffaux
    Offline

    Tiffaux Donator

    Joined:
    May 30, 2018
    Messages:
    201
    Likes Received:
    189
    Gender:
    Female
    Location:
    TulipsHQ Ch15
    Country Flag:
    Level:
    12x
    Guild:
    Tulips
    I can't adjust any cell, so can't fill out my own hp/mp

    The formula's do work tho, I can see them now when I click the F columns
     
  9. Inusama
    Offline

    Inusama Donator

    Joined:
    Mar 26, 2015
    Messages:
    1,654
    Likes Received:
    5,271
    Gender:
    Male
    Country Flag:
    IGN:
    Inusama
    Level:
    200
    Guild:
    Oblivion
    Strange, it let me edit all the boxes when I made a copy in another browser on a different google account.. I’ll look into it more later today!
     
    Tiffaux likes this.
  10. Tiffaux
    Offline

    Tiffaux Donator

    Joined:
    May 30, 2018
    Messages:
    201
    Likes Received:
    189
    Gender:
    Female
    Location:
    TulipsHQ Ch15
    Country Flag:
    Level:
    12x
    Guild:
    Tulips
    Oh it is possible to copy your Google Sheet so when pasting it in a new (own) Google Sheet it's possible to adjust the cells.

    So to be clear, stated differently: One can now open the link, copy all, paste in new Google Sheets, and play around with it.
     
  11. Tect
    Offline

    Tect Well-Known Member

    Joined:
    Aug 15, 2017
    Messages:
    3,200
    Likes Received:
    5,091
    Country Flag:
    IGN:
    DTect
    Level:
    200
    Guild:
    Manon
    u can just do this to get a copy of everything
    upload_2020-5-11_19-44-25.png
     
  12. Tiffaux
    Offline

    Tiffaux Donator

    Joined:
    May 30, 2018
    Messages:
    201
    Likes Received:
    189
    Gender:
    Female
    Location:
    TulipsHQ Ch15
    Country Flag:
    Level:
    12x
    Guild:
    Tulips
    Lol right hahaha *facepalm*
     
  13. Inusama
    Offline

    Inusama Donator

    Joined:
    Mar 26, 2015
    Messages:
    1,654
    Likes Received:
    5,271
    Gender:
    Male
    Country Flag:
    IGN:
    Inusama
    Level:
    200
    Guild:
    Oblivion
  14. 0ceon
    Offline

    0ceon Member

    Joined:
    May 25, 2016
    Messages:
    16
    Likes Received:
    9
    Gender:
    Male
    Country Flag:
    IGN:
    Maguiber
    Level:
    74
    Guild:
    Excerpt
    a few pointers:

    -Format the display of the cells for %-based pots so that it is in decimal, but is displayed with % (that way, you can remove the white-inked decimal values)
    -Fix formulas for %-based pots so that they are decimal, but are displayed in %.
    -Fix for formulas: any formula that references B2/C2, switch to reference to static ($B2 and $C2)

    After you've the aforementioned, you can sort all rows by a specific column (Column F: the column that shows the healing/meso ratio)
    so the spreadsheet will displayed the best pots for you at the top.
     
  15. 0ceon
    Offline

    0ceon Member

    Joined:
    May 25, 2016
    Messages:
    16
    Likes Received:
    9
    Gender:
    Male
    Country Flag:
    IGN:
    Maguiber
    Level:
    74
    Guild:
    Excerpt
    Here, I made some changes:

    -You can select if you want to prioritize only HP/MP/Both (this will change the cost-ratio to reflect only on the chosen choice)
    -Added Filters to sort most data (except elixirs, and onyx apples) by most-efficient cost ratio
    To apply the filter (so that the data gets sorted), go to "Data" -> "Filter views", and there should be a filter called "regen-cost ratio"

    Link for my version of spreadsheet
     
    Last edited: May 12, 2020
    Tiffaux likes this.
  16. onekeystory
    Offline

    onekeystory Donator

    Joined:
    May 4, 2019
    Messages:
    396
    Likes Received:
    824
    Gender:
    Male
    Country Flag:
    IGN:
    Raffinato
    Level:
    200
    Guild:
    Savior
    Amazing sheet, i didnt even know mushroom miso ramen is a viable option
     

Share This Page