View previous topic :: View next topic 
Author 
Message 
gizmo OOo Advocate
Joined: 13 Oct 2004 Posts: 248 Location: Everywhere

Posted: Fri Nov 11, 2005 4:31 pm Post subject: Trouble with a math function in Calc... 


I am using a spreadsheet to calculate numbers for a game. Right now I have it set up like this:
Code: 
Attribute Val Cost XP Needed
0 50 0 <base value
1 51 51
2 52 103
3 53 156
4 54 210
5 55 265
6 56 321
7 57 378
8 58 436
9 59 495
10 60 555

instead of needing a large spreadsheet with 50+ formulas, I want it to be similar to this:
Code: 
Attribute Difference Base Cost Cost XP Needed
10 50 60 555

Where the first two cells would be the only cells requiring data to be entered, the other two would be formulas(one to calculate the cost of the current item, the other to calculate the cost of all intermediate cells. IE if you input 10 as the value for attribute difference, it would calculate the XP cost for cells 110 and add them together like the top example does.
Deleted spam links  floris v, moderator
Last edited by gizmo on Sat Mar 19, 2011 5:32 am; edited 2 times in total 

Back to top 


ms777 Super User
Joined: 07 Feb 2004 Posts: 1355

Posted: Fri Nov 11, 2005 4:47 pm Post subject: 


Assuming that Attribut Difference is Col A and Base Cost is Col B, enter =A2+B2 in C2, and =A2*((A2+1)/2+B2) in D2
ms777 

Back to top 


gizmo OOo Advocate
Joined: 13 Oct 2004 Posts: 248 Location: Everywhere

Posted: Sun Nov 13, 2005 12:06 am Post subject: 


ms777 wrote:  Assuming that Attribut Difference is Col A and Base Cost is Col B, enter =A2+B2 in C2, and =A2*((A2+1)/2+B2) in D2
ms777 
Thanks, that worked pretty well! Now what if I wanted to (in another sheet) reverse that to derive the value of D2 from values input into A2 and B2?
Deleted spam links  floris v, moderator
Last edited by gizmo on Sat Mar 19, 2011 5:53 am; edited 1 time in total 

Back to top 


bEuwe General User
Joined: 23 Sep 2006 Posts: 15

Posted: Sun Dec 24, 2006 1:33 am Post subject: 


gizmo wrote:  Thanks, that worked pretty well! Now what if I wanted to (in another sheet) reverse that to derive the value of D2 from values input into A2 and B2? 
I'm not aware of a mathematical formula that readily gives you the reverse. I think you can only derive that by using a calc equivalent of a while loop (and I don't think that is possible), and essentially you're applying that formula given above multiple times, to see if the result matches your input.
Also: you want to input one variable (the total XP), and expect to be able to uniquely identify two separate and distinct variables (attribute diff & cost). That is something that is not possible. You should be able to express one in terms of the other.
Why not just go with a lookup table? It makes understanding what's going on much easier. 

Back to top 


pracslipkerm General User
Joined: 08 Sep 2005 Posts: 29 Location: NSW, Australia

Posted: Fri Nov 02, 2007 4:09 pm Post subject: Old Thread  Different Solution 


I know this is fairly old but another solution is to use a user function (I wrote this in BASIC) to take the 2 Variables and do the required iteration. I cant attach the file but I created this in the My Macros \ Standard \ module1 it can also go in the document \ standard \ module.
Code: 
REM ***** BASIC *****
Function XP(ADiff as Integer,BCost as Integer) as Integer
Dim Cost as Integer
Dim XPN as Integer
Cost = BCost + 1
For I = 1 to ADiff
Let XPN = XPN + Cost
Let Cost = Cost + 1
Next I
XP = XPN
End Function

Spreadsheet then has a cell for each variable (lets say A2 and B2) then another cell contains (in this example)
This should give desired result _________________ Steve Griffiths
Ubuntu, Win XP
"Not Everything that Counts can be Counted, and Not Everything that can be Counted, Counts" Einstein 

Back to top 


