Total players online: 7



Take the Tour | Site Map

It is currently Tue Dec 12, 2017 12:35 am

All times are UTC - 8 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
PostPosted: Thu Mar 30, 2017 3:20 am 
Offline
Legendary Scripter
Legendary Scripter
User avatar

Joined: 12/23/2003 03:27
Posts: 1144
Location: Seige Perilous

The calculation for swing speed is a follows:
http://www.uoguide.com/Swing_Speed_Increase
((Base Weapon Speed - Stamina Ticks) * (100.0 / (100 + Swing Speed Increase))) = "x" ticks

I'm wanting a spreadsheet that can list the different weapons and show what their top dps will be with different SSI levels.
I've calculated the stamina based weapon swing ticks on having 90 stamina, which equates to a 3 tick reduction.

so far I made this:

Weapon_________base speed_base dmg_base wep ticks_stam wep ticks_swing / sec__base dps____ssi 10__ssi 20__ssi 30__ssi 40__ssi 50
Halberd_________4.25_______18___________17____________ 14_______3.5__5.1428571___5.71__6.17____6.69__7.20____7.79
Radiant Scim_____2.5_______13___________10______________7______1.75__7.4285714__8.25___8.91____9.66__10.40___11.26

but excel rounds to closest and UO always rounds down, so some of the math is off. I also can't figure out how to limit the low end of weapon ticks per swing to be 5.

I'm looking to see what weapon with good SSI will be best dps overall, with the calulated 90 stam and then also with different stam.

50% SSI equates to weapon speed ticks * .666
1st weapon 14 * .666 = 9.324 which = just over 2.0 sec per swing which is not faster that UO cap
2nd weapon 7 * .666 = 4.662 which is just under the 5 tick swing speed. So therefore the 11.26 dps calc can not be achieved.

How can I do excel formulas to say if weapon ticks < 5 then weapon ticks = 5 and have it display at SSI 50 not the hard code calculated dps but the dps based on the limit of 5 ticks, or 1.25 sec per swing.

_________________
While a = a
{
Scanjournal 1
If Daemon_is_attacking_you in #journal || ( you_are_attacking in #journal && Daemon in #journal )
Terminate UO
}


Top
  ProfileShow poster's scripts in the public script library 
PostPosted: Thu Mar 30, 2017 4:45 am 
Offline
Script Scribe
Script Scribe
User avatar

Joined: 10/02/2003 14:10
Posts: 79

Groups:
I will not code it for you, as 1) you didn't provide your formula anyway, and 2) I would rather teach you how to use Excel...I am a professor after all. =)

To get the Floor (Or Ceiling) of a decimal number, you must use the functions built into Excel!

Code:
  1. =FLOOR(A1,1)


If A1 contains 4.6, this will put 4 into the cell.

Now, if you are trying to calculate a value and use some logic or limit on your value, you need to dive into expressions.
Consider that I have two values, X and Y. I need to multiply them together, but if X is ever greater than 2, I must use 2, and if the answer is ever over 10, I must say 10, but if the value is ever over 20, I want to leave the cell filled with X's.
Therefore this X*Y=Z has some rules and we want to make one cell calculate our answer.

So, if X > 2, then X = 2
If Z > 20, Z = "XXX"
If Z > 10, Z = 10

Imagine then a table (testing our corner cases and just validating that it works) This will not copy/paste into Excel, because of the website trimming excessive tabs or spaces or something.
1 4
2 4
3 3
5 2
1 10
2 10
3 10
2 12
3 14

Putting =IF(A1>2,2,A1)*B1 into C1 and filling down the row, we get:
1 4 4
2 4 8
3 3 6
5 2 4
1 10 10
2 10 20
3 10 20
3 14 28

Going a step further, to filter out the larger values (>10 and > 20) we need to create a bigger logical test. Every time we use A1, we must get the correct value, then if Z if > 20 we put in X's and if > 10, we round to 10, so we have to embed an if/elseif/else in our formula.

Change cell C1 to:
Code:
  1. =IF(IF(A1>2,2,A1)*B1>20,"XXX",IF(IF(A1>2,2,A1)*B1>10,10,IF(A1>2,2,A1)*B1))


Giving us:
1 4 4
2 4 8
3 3 6
5 2 4
1 10 10
2 10 10
3 10 10
2 12 XXX
3 14 XXX

So, the final step is using the FLOOR to round values down.

Our formula, because every time we use A1 we must make sure that it is <= 2 and rounded, the equation explodes in size. Feel free to use Notepad++ or something and paste into Excel if you do this. And don't think this is uncommon, I have had formula that contained 20, 30, 40 if statements in it.

Code:
  1. =IF(IF(FLOOR(A1,1)>2,2,FLOOR(A1,1))*B1>20,"XXX",IF(IF(FLOOR(A1,1)>2,2,FLOOR(A1,1))*B1>10,10,IF(FLOOR(A1,1)>2,2,FLOOR(A1,1))*B1))


Expanding our table (shows that floor works the same on 1.1 (1 if rounded) as it would 1.6 (2 if rounded))

1 4 4
2 4 8
3 3 6
5 2 4
1 10 10
2 10 10
3 10 10
2 12 XXX
3 14 XXX
1.1 4 4
2.2 4 8
3.3 3 6
5.5 2 4
1.1 10 10
2.2 10 10
3.3 10 10
2.2 12 XXX
3.3 14 XXX
1.6 4 4
2.6 4 8
3.6 3 6
5.6 2 4
1.6 10 10
2.6 10 10
3.6 10 10
2.6 12 XXX
3.6 14 XXX

Using hidden cells, you can make your formula less complex or at least shorter, but that is another lecture...


Top
  ProfileShow poster's scripts in the public script library 
PostPosted: Sat Apr 01, 2017 3:37 am 
Offline
Legendary Scripter
Legendary Scripter
User avatar

Joined: 12/23/2003 03:27
Posts: 1144
Location: Seige Perilous

Being really inexperienced with this type of math and coding, I think I'm understanding the 1st section where a value is being limited:

floor means it will always round down, and ceiling will always round up?

=IF(A1>2,2,A1)*B1
I hope I got this right,
if A1 > 2
use value 2
else
use value A1
endif
and the whole IF statement becomes the answer of itself and is then multiplied by B1...

I'm going to work on making this work in excel, will post again if I still get stuck on it.

Thank you

==========
EDIT
==========
Here's the equation I implemented to round down the result of adding the SSI to the weapons Swing Ticks...
C4 is the weapon damage and E4 is the base weapon swing ticks
=C4/(FLOOR(E4*(100/110),1)/4)
now the if statement, would have to look like this?
=C4/IF(FLOOR(E4*(100/110),1)>5,5,(FLOOR(E4*(100/110),1))/4)

_________________
While a = a
{
Scanjournal 1
If Daemon_is_attacking_you in #journal || ( you_are_attacking in #journal && Daemon in #journal )
Terminate UO
}


Top
  ProfileShow poster's scripts in the public script library 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 8 hours


Who is online

Users browsing this forum: ickmanuriardri and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group
Hosted by CalWeb Internet Services, Inc.