| 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 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 |
|
| 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 |
|
|
|
