[Archive] Cost Calculator and Refinement Calculator - Guides and Quests - WarpPortal Community Forums

Jump to content


Photo

[Archive] Cost Calculator and Refinement Calculator


  • This topic is locked This topic is locked
32 replies to this topic

#1 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 12 February 2017 - 01:56 PM

About

This program calculates the refinement chances, as well as number of equipment and ores needed to refine an equipment. The ores, equipment and refinement range are chosen by the user. The user can then use the results as input for the cost calculator sheet to see how much he/she would spend on the process. This program takes into account everything that can happen when attempting to refine an equipment: upgrading, downgrading, breaking, and staying at same refine level. This program was made so that it can be used on any RO server, the user just need to change its configurations. This program was made on google sheets. Make a copy of it.

 

How to use:

There are only 3 sheets you'll need to manage if you don't want to change anything.

  • Main sheet:
    • Select the equipment you want to use, the initial and final refine levels
    • Choose if you want to inform the "success %", the "failure %", or the "nº of equip", and type in the value.
    • Press the giant equal button. It will calculate the 2 other data based on the one you informed. Example, if you chose "success %" it will calculate and show "failure %" and "nº of equip". It will also calculate the averages, and the total amount of ores you will need.
  • Ores Used sheet:
    • Select the ores you want to use for each refine level. The ores shown on the main sheet after pressing the equal button are selected by this sheet
  • Costs Calculator sheet: After you got the total amount of equipment and ores for your refining, you can use this sheet to calculate how much you will spend.
    • Add or remove the resources(equipment and ores) used under the "Resource" column
    • Type in the number of them used under "Nº of resources to use" column.
    • Type in the individual cost for each one(in any currency, zeny/cash/etc) under the "Individual Cost" column. Then under the "Total sum" you will have the total amount of zeny you will spend

 

Proof that the program works

With the refinement simulation program, you can see that the numbers matches the averages calculated on the main sheet. Save the file as "programa.cpp" then compile it using a C++ compiler, like g++. The command is "g++ programa.cpp -std=c++11 -o programa". If you're under windows, you can install g++ using MinGW or Cygwin

Example:
So here's the result of my program trying to refine a lvl 4 weapon from +0 to +17 using enriched oridecon and HD Bradium(results are in descending order, from +17 to +0):
"17: 0.014112000032579636|16: 0.20160000046542337|15: 2.880000006648905|14: 38.46445723165802|13: 447.3257153184317|12: 5144.4221261623725|11: 59161.03085086769|10: 680352.0311849788|9: 0.07056|8: 0.1764|7: 0.44099999999999995|6: 0.63|5: 0.9|4: 1|3: 1|2: 1|1: 1|0: 1"
and the result of the simulation(results are in ascending order, from +0 to +17), running it 100 thousand times(took like 5 hours on my machine lol):
100000 100000 100000 100000 100000 89823 62950 44053 17612 7122 4049314828 1659344300 517772410 45034477 3872288 289190 20134 1426 0 0 0

So the success chance to get to +17, according to my program, is 0.014112000032579636. Running the simulator 100 thousand times, it got to +17 1426 times, which is very, very close to the value expected(0.014112000032579636 * 100000 = 1411). To +10 and +11 the simulation overflowed because they got over 4294967295(max unsigned 32-bit int)

SHEETS:
iRO renewal refinement
iRO pre-renewal refinement
kRO renewal refinement

FAQ
Q: OMG if I select the final refine level too quickly I can select an invalid value. What now?
A: If this happens, you will either get a message error or the dropdown box will go red. Just type in a valid value.

Q: I clicked on the blue equal button and I got a message error saying the function could not complete or it says "Running Script" forever. What now?
A: If this happened, it is likely that you clicked on the button before it said "All changes saved in drive". Reload the page.

Q: If I type 100 in the "success %" I get #NUM! or infinity as the n of equip and infinity as the number of ores. Why?
A: To reach 100% success you would need an infinite amount of ores, and an infinite amount of equipment. Unless you're refining in the safe levels(+0->+1 for instance) or there is a safe refine level(for instance, say you can't go below +10. If you try to refine between +10 and any other refine level, the success chance will always be 100%, because you will never break the equipment, and given that you have enough ores, you will always reach the refine level you want).

Q: If I type anything less than 100 in the "success %" I get 0 or NaN as n of equip and no ores or NaN as number of ores. Why?
A: You are probably trying to refine with initial and final refine levels being between safe refine levels(+1->+2 for instance) or doing something equivalent(+10->+11 with bless of blacksmith, on kRO spreadsheet). If you try to refine between +12 and +15, using HD Bradiums for example, you're equipment will never break on iRO. That is because you can never go below +10 using HD ores, so even if you downgrade from +12 to +10, eventually you will get to +15 given you have enough ores.You're equipment can't break in that situation, consequently the chance of success is always 100%. On these situations, change the number of equipment only. Actually, just leave the number of equipment at 1, and check how many ores you need.

Q: If I type in 100000 number of equipment(or a really high value) and press the equal button, I get 100% success chance, but if I then type in 100% success chance and press the equal button again right after, instead of getting 100000 number of equipment, I get "#NUM!" or infinity number of equipment and infinity number of ores. Why?
A: When you first type in 100000 number of equipment(or a really high value) and press the equal button, the success chance is so high that the machine can't represent it and rounds it to 100%. This is just a precision problem. You will only ever have 100% success chance if you're refining between safe levels(+1->+2, any range where the initial refine level is >= +10 using HD ores on iRO, like +12->+15(it will never go below +10, given that you have enough ores eventually you will reach +15), etc)

Q: What is a good success % to try out?
A: It depends on how deep is your pocket. I consider a reasonable success % to be >= 60% and <= 70%. However if your pocket allows it...the more the merrier.

Q: What are all those abbreviations when choosing the ores?
A:

    1-iRO Renewal and kRO renewal Spreadsheets:
        1.1-WS->Whitesmith. They are the regular ores with rates buffed as if you were using Whitesmith's Weapon Refine skill at job level 70.
        1.2-E->Refinement Event Rates. They are the ores buffed by the refinement event rates.

    2-iRO Renewal:
        2.1-H->Enriched Hammer. They are the ores buffed by Enriched Hammer so the equipment doesn't break and just downgrade instead.
        2.2-EH->Refinement Event Rates + Enriched Hammer. They are the ores buffed by the refinement event rates and by Enriched Hammer.

    3-kRO Renewal:
        3.1-B->Bless of Blacksmith. They are the ores buffed by Bless of Blacksmith so the equipment doesn't break or downgrade.
        3.2-EB->Refinement Event Rates + Bless of Blacksmith. They are the ores buffed by the refinement event rates and by Bless of Blacksmith.

Q: On the iRO Renewal Spreadsheet, you know that you can only start using enriched hammer and HD Oridecon/Elunium at +7, right? Then why can you select those ores at levels below +7?
A: Because although you can only start using them at +7, if the equipment falls below +7 and you don't exit the npc, you are still able to use the enriched hammer or HD Oridecon/Elunium. The right way to approach this situation is first refine from +0 to +7 without the hammer(on the ores used, below +7, don't select enriched hammer or HD Oridecon/Elunium), take note of the amount of ores required, then change to refine from +7 to +10(or whatever is the final refine level) with the hammer(on the ores used, below +7, select to use enriched hammer or HD Oridecon/Elunium, since you will be able to use them even if below +7 if you don't exit the npc). Take the amount of ores now (+7 to +whatevs) and sum with the amount you got before(+0 to +7). This way you will get the right amount of ores you'll need to use.

Q: On the iRO Renewal Spreadsheet, you know that using a safe cert requires an oridecon or elunium, right? Does the total amount of ores shown take that information into account?
A: Yes, that information is stored in the "Ore Dependency" sheet. So when using a "Safeto7" cert, the line "{"Safeto7":1, "Oridecon":1}" says that it will use 1 safeto7 cert but also 1 oridecon. This same logic applies to bless of blacksmith on the kRO spreadsheet.

Q: I'm trying to adapt one of the spreadsheets to the server I play in, but when I delete an equipment(remove a column) or when I delete anything, it doesn't seem to have any effect.
A: Blame Google. The thing is that there's a code where it says "OnEdit", which means, when you edit stuff, it does something. The problem is this code only detects when you add stuff, or modify stuff. But when you delete stuff, the code doesn't trigger. I can't do anything about this, the workaround is to simply reload the page. Generally, if all you want to do is change the numbers(for example, the success chances), or add equipment, or add ores, then you don't need to reload the page. But if you decide to, say, remove equipment types(remove shadow gear for example), then you would need to reload the page. To keep it simple...if you're not removing any column or row you won't need to reload the page. Just change everything you need to change and after you're done, reload the page.


Edited by JuicyJuices, 12 December 2017 - 08:04 PM.

  • 4

#2 Kusanagisama

Kusanagisama

    Too Legit To Quit

  • Members
  • 4243 posts
  • Playing:Ragnarok Online
  • Server:Chaos

Posted 12 February 2017 - 06:56 PM

Hmm...

 

Very interesting, but not exactly intuitive. This one is simpler.

 

http://crmindexer.org/eventcalc/


Edited by Kusanagisama, 12 February 2017 - 06:57 PM.

  • 0

#3 Bohem

Bohem

    Too Legit To Quit

  • Members
  • 1939 posts
  • LocationMage Class Forum..?
  • Playing:Ragnarok Online
  • Server:Chaos

Posted 13 February 2017 - 04:59 AM

for me, its seems like i have less then 1% success chance lol


  • 0

#4 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 13 February 2017 - 06:30 AM

 

Very interesting, but not exactly intuitive. This one is simpler.

Well, it may not seem intuitive because of the length of the video, but it is. I just stutter a lot and my english is bad, but the calculator is simple, really. You calculate on the main sheet, if you need to add new ore types or change percentages you change the other sheets. As I said on the video, I made the calculator as configurable as possible, so that you can play on different servers with it. You can change the percentages, add more ores(you could add safe certs, it would be like a ore with 100% chance refinement), etc. I could've made one a lot simpler, like the one you showed, but that was not my intention. You can use mine on any RO server you want to play in. Well, thank you for the feedback.

EDIT
In addition, on mine you also calculate the chance that you will succeed on refining.

Anyway, use the one you prefer of course :).

EDIT
i've changed the code a little bit so that you never have to reload the page after changing information on other sheets, even if it's the equipment data sheet. I think that was a little confusing, when to reload the page or not. Well, now you never have to reload it.


Edited by JuicyJuices, 13 February 2017 - 08:25 AM.

  • 1

#5 Kusanagisama

Kusanagisama

    Too Legit To Quit

  • Members
  • 4243 posts
  • Playing:Ragnarok Online
  • Server:Chaos

Posted 13 February 2017 - 12:15 PM

Very interesting indeed. Thanks for sharing your work. :no1:


  • 1

#6 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 13 February 2017 - 02:46 PM

 

Very interesting indeed. Thanks for sharing your work

Glad you liked it! The length of the video probably discourages most people, I think I might do it again and somehow do a 5min length video only. But gosh with my english that's gonna be hard.


  • 1

#7 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 13 February 2017 - 03:55 PM

Changed the main video to a 8min video instead of 45min lol. That's the best I can do.


  • 1

#8 PervySageMarty

PervySageMarty

    Too Legit To Quit

  • Members
  • 3956 posts
  • LocationUnderground like a wild potato
  • Playing:Ragnarok Online
  • Server:Chaos

Posted 14 February 2017 - 01:00 AM

My probability formula is better:

-It gonna work!
-It not gonna work!

With just two even split probabilities its more digestible to me than looking at actual %
  • 2

#9 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 14 February 2017 - 03:46 AM

My probability formula is better:

-It gonna work!
-It not gonna work!

With just two even split probabilities its more digestible to me than looking at actual %

 

Hahaha! I like to know my chances :blaugh:


  • 0

#10 Kusanagisama

Kusanagisama

    Too Legit To Quit

  • Members
  • 4243 posts
  • Playing:Ragnarok Online
  • Server:Chaos

Posted 14 February 2017 - 03:53 AM

My probability formula is better:

-It gonna work!
-It not gonna work!

With just two even split probabilities its more digestible to me than looking at actual %

 

:heh: :heh: :heh:


  • 0

#11 TheUnlimited

TheUnlimited

    I made it Off Topic

  • Members
  • 73 posts

Posted 14 February 2017 - 06:17 AM

Thank you for sharing your work!

 

I only wish my luck were better.  I just blew up EVERY SINGLE Principles of Magic I'd stored up for the last two weeks, and my alt account's Oridecon supply with it.  With my Mastersmith, no less.  So much for a cheap leveling weapon.    :p_sick: 


  • 1

#12 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 14 February 2017 - 07:20 AM

 

Thank you for sharing your work!

Glad you liked it!

 

 

I only wish my luck were better.  I just blew up EVERY SINGLE Principles of Magic I'd stored up for the last two weeks, and my alt account's Oridecon supply with it.  With my Mastersmith, no less.  So much for a cheap leveling weapon.

I feel you =/


  • 0

#13 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 14 February 2017 - 07:27 AM

All spreadsheets:

I've deleted all extra rows and column for all sheets except the main sheet and cost calculator sheet. Although this doesn't affect the program on this particular case, it is actually important to let only the tables on those sheets, otherwise there are situations that the program won't work. So if you plan on changing the sheet, adding new ores, removing others, etc, I recommend downloading the sheets again or removing the extra columns and rows yourself. (if you're curious about why it is like this...it's because I just discovered a bug on google sheets where sometimes empty columns and rows are identified as rows/columns with data - even though they shouldn't. So my program suddenly stopped working for no apparent reason when I adapted the program for a pre-renewal server)


Edited by JuicyJuices, 10 June 2017 - 12:24 PM.

  • 0

#14 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 14 February 2017 - 10:31 AM

pre-renewal version added!


  • 0

#15 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 15 February 2017 - 02:11 PM

All spreadsheets:

Fixed a major bug, where the number of ores being calculated was wrong. Also fixed 2 minor bugs. Please download it again, if you find any bugs please report them.(there shouldn't be any more major bugs though, I will get mad at myself if someone finds any because I did my best to post without any major bugs to begin with lol :P)


Edited by JuicyJuices, 10 June 2017 - 12:23 PM.

  • 0

#16 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 21 February 2017 - 01:52 PM

I made a iro renewal and pre-renewal versions. Meaning all the ores and percentages are already configured! All you may have to do is change the ores you want to use under "Ores Used" sheet.


  • 0

#17 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 22 February 2017 - 06:50 AM

All spreadsheets:

Added a few notes on the sheets. Hopefully it will help the user understand what he can do. The video should still be watched, but these notes are an extra guidance. Hopefully having 3 pre-configured spreadsheets also helps the user see how he can change things and adapt to his liking in case he/she wants to play on a different server.

 

But if you have any questions at all, please feel free to ask them! I know my program has many sheets and it may make you feel overwhelmed! I'll help you understand, it's quite easy ;)

 


Edited by JuicyJuices, 10 June 2017 - 12:22 PM.

  • 0

#18 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 28 February 2017 - 08:43 AM

All spreadsheets:

Updated the sheets so that the success% and failure% fields displays the maximum number of decimal places possible. Updated main post with a FAQ.


Edited by JuicyJuices, 10 June 2017 - 12:22 PM.

  • 0

#19 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 11 March 2017 - 01:14 PM

All spreadsheets:

  • Improved how the user can change the "Ores Used" sheet. It now has drop-down lists. So instead of typing in the values, the user can select them instead. This is less prone to user mistakes. To do this, the way the "Equipment Data" sheet works had to be changed.
  • Also added new ore types:

Enriched Oridecon E
Enriched Elunium E
Bradium E
HD Bradium E
Carnium E
HD Carnium E

These ores refer to the refinement event rates. So when there is a refinement event going on, choose the ores that end with "E" so that the calculations take into account the event rates.
If the event rates change next time, the user only needs to change the event ores' rates from the "Success Rates" sheet.


Edited by JuicyJuices, 10 June 2017 - 12:21 PM.

  • 0

#20 thesifer

thesifer

    Amateur Blogger

  • Members
  • 283 posts
  • Playing:Ragnarok Online
  • Server:Chaos

Posted 11 March 2017 - 02:06 PM

Thanks for making a refinement calculator. I am pretty sure you spent lots of time in making it.
 
I have a question about a refinement calculator though. There are two main refinement calculators I usually use
1. Dr. Azzy's Upgrade Cost Calculator - http://drazzy.com/ro...c/upgrade.shtml
 
These two don't give the same result for +8 and +9. Do you know which one is more accurate? 
 
Can you use your calculation to check which calculator is trustworthy?
 

Edited by thesifer, 11 March 2017 - 10:02 PM.

  • 0

#21 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 11 March 2017 - 03:29 PM

Hey @thesifer. I am not familiar with those calculators, they don't seem to show the success %, only costs. Or maybe I'm missing something. So, if you're asking about the cost, the cost depends on the equipment you want to refine, the interval, and the ores. So like, there's the "cost calculator" sheet right? You get the total ores from the main sheet and replace the values in "n of resources to use" in the "cost calculator" sheet. You can and should also update the values for each individual resource, as this varies from server to server(include the npc fees).

 

So to answer your question, I think it would be easier if you showed my a screen shot of your "cost calculator" tab from my calculator and screen shots from both calculators you presented. And what weapon level and refinement range those calculators are working with, because I can't seem to figure it out :P. I mean the first one you showed says armor or lvl 4 weapon. The second though, I can't find the information.

 

I can guarantee that mine is trustworthy though haha. I even bothered to make a video proving so, which is the second video, from 29:56 onwards.

 

EDIT

So, Azzy's calculator seems right. It more or less matches the result with 61% success chance on my program. The other one though gives me a completely different result. I would go with Azzy's between those 2.

 


Edited by JuicyJuices, 13 March 2017 - 03:13 AM.

  • 0

#22 thesifer

thesifer

    Amateur Blogger

  • Members
  • 283 posts
  • Playing:Ragnarok Online
  • Server:Chaos

Posted 11 March 2017 - 10:08 PM

Hey @thesifer. I am not familiar with those calculators, they don't seem to show the success %, only costs. Or maybe I'm missing something. So, if you're asking about the cost, the cost depends on the equipment you want to refine, the interval, and the ores. So like, there's the "cost calculator" sheet right? You get the total ores from the main sheet and replace the values in "n of resources to use" in the "cost calculator" sheet. You can and should also update the values for each individual resource, as this varies from server to server(include the npc fees).

 

So to answer your question, I think it would be easier if you showed my a screen shot of your "cost calculator" tab from my calculator and screen shots from both calculators you presented. And what weapon level and refinement range those calculators are working with, because I can't seem to figure it out :P. I mean the first one you showed says armor or lvl 4 weapon. The second though, I can't find the information.

 

I can guarantee that mine is trustworthy though haha. I even bothered to make a video proving so, which is the second video, from 29:56 onwards.

 

EDIT

So, Azzy's calculator seems right. It more or less matches the result with 61% success chance on my program. The other one though gives me a completely different result. I would go with Azzy's between those 2. Although it could be that I didn't figure out how to configure the other calc correctly. Don't wanna trash anybody's work here. But you know, since I made my own calc I recommend it over others hahaha. It is highly configurable, and works for any RO server. It takes into account upgrading, downgrading, breaking, and remaining at same level, all possibilities. The price that comes with that is it is not as straightforward as the others, but it's not hard to get the hang of it either, after you watch the 8min video.

Thanks a lot for your reply. I think I should find some time this week to watch your video. It should be a good reason for me to procrastinate a little bit. xD

 

I will let you know if I have any thoughts on your calc. Thanks!


  • 0

#23 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 13 March 2017 - 04:28 AM

iRO Renewal Spreadsheet:

Added safe to 10 certs. If I'm missing any ores, please feel free to say it.


Edited by JuicyJuices, 10 June 2017 - 12:16 PM.

  • 0

#24 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 30 April 2017 - 04:21 PM

All spreadsheets:

  1. Added shadow gear
  2. Made it so that ores that uses other ores are taken into account in the "totalOres" calculation. Example: safeto7 cert also requires 1 oridecon, bless of blacksmith requires 1/2/4/7/12 bless of blacksmith + main ore(this is on kRO preset though)). This required the creation of another sheet, "Ore Dependency".
  3. minor issues fixed
  4. All sheets are hidden by default, except for the main, ores used and cost calculation sheets. These 3 sheets are the only ones the end user has to manage if they don't want to change anything.

Note to self: Stopped using project properties and use document cache instead. Project properties can only hold up to 9KB per key, which led
to the program stop working on the kRO preset unexpectedly because one of the keys was 14KB lmao.
Document cache can hold up to 100KB per key, more than enough for this application.


Edited by JuicyJuices, 10 June 2017 - 12:16 PM.

  • 0

#25 JuicyJuices

JuicyJuices

    I made it Off Topic

  • Members
  • 34 posts

Posted 02 June 2017 - 08:59 AM

All spreadsheets:

Line 91 of "calculations.gs", changed

var ores = JSON.parse(oreDependencyJson[Object.keys(oreDependencyJson)[0]][equipment][oresUsedJson[Object.keys(oresUsedJson)[0]][equipment][refineLevels[i]]][i]);

to

var ores = JSON.parse(oreDependencyJson[Object.keys(oreDependencyJson)[0]][equipment][oresUsedJson[Object.keys(oresUsedJson)[0]][equipment][refineLevels[i]]][refineLevels[i]]);

Please copy the spreadsheet again. This small error was introduced on v3, making so that the total ores calculated was only working if you started refining at +0(it's still called v3 but the code has been updated, so just copy the spreadsheet again)

kRO spreadsheet

Changed "Ore Dependency" sheet, when upgrading from +11 to +12 and using bless of blacksmith, it's required 11 bless of blacksmith, not 12,

so changed

{..., "Bless of Blacksmith":12}

to

{..., "Bless of Blacksmith":11}

Edited by JuicyJuices, 02 June 2017 - 09:15 AM.

  • 1




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users