[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
gizmo

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 1-10 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
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
gizmo

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
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.
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)
 Code: =XP(A2;B2)

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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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 vote in polls in this forum