#1   Report Post  
JemyM
 
Posts: n/a


Hi! I am new to the forum, and I love playing with Excel so I might be a
frequent visitor here, I dont know. :)

I have this nifty Excel problem that I cannot solve. I need a formula
that calculates the following for a roleplaying generator:

Value A: Attribute (that decides 1 point or 3 points)
Value B: Old Skill (current value)
Value C: XP Points (that must be spent)
Value D: New Skill (desired value)

Description: In an rpg we are playing you upgrade your Old Skill by
spending XP Points. As long as the skill is lower than your Attribute,
you pay 1 XP per Skill. When you go above your attribute you pay 3 XP
per skillpoint.

I want a formula that allow a player to write his wanted new skillevel
(D) in a cell.

The stylesheet will look at her old skillvalue (B) to determine if the
new value is greater than her old one. If it is, it will determine how
many points must be spent to reach the New Skill and show the result.
The issue is, *that each point above her Attribute (A) is count as '3'
instead of '1'*.

For example, a player have 15 Dexterity (Attribute) and 10 in Dance
(Skill). They want to spend XP to bring Dance up to 20 (Desired Value).
They spend five points to bring the skill up to 15, then 5x3=15 to bring
the skill up to 20. From 1020 with 15 dexterity therefore costs 20 XP
Points.

My greatest problem with my own formula is when the Attribute (A) is
potentially lower than their Current Skill (B), Excel starts to count
negative points ending up in a greater cost than planned. I feel that
my lack of mathskill limit me from finding a way that counts negative
values as "0".


--
JemyM
------------------------------------------------------------------------
JemyM's Profile:
http://www.excelforum.com/member.php...o&userid=26945
View this thread: http://www.excelforum.com/showthread...hreadid=401682

  #2   Report Post  
Barb Reinhardt
 
Posts: n/a
Default

I'm having some difficulty wrapping my brain around what you want. Can you
give a table with specific examples of what you expect? Give me the
following examples:

New skill old skill
New skill = old skill
New skill < old skill

Also show the variation of what can happen for attributes as well.

"JemyM" wrote in
message ...

Hi! I am new to the forum, and I love playing with Excel so I might be a
frequent visitor here, I dont know. :)

I have this nifty Excel problem that I cannot solve. I need a formula
that calculates the following for a roleplaying generator:

Value A: Attribute (that decides 1 point or 3 points)
Value B: Old Skill (current value)
Value C: XP Points (that must be spent)
Value D: New Skill (desired value)

Description: In an rpg we are playing you upgrade your Old Skill by
spending XP Points. As long as the skill is lower than your Attribute,
you pay 1 XP per Skill. When you go above your attribute you pay 3 XP
per skillpoint.

I want a formula that allow a player to write his wanted new skillevel
(D) in a cell.

The stylesheet will look at her old skillvalue (B) to determine if the
new value is greater than her old one. If it is, it will determine how
many points must be spent to reach the New Skill and show the result.
The issue is, *that each point above her Attribute (A) is count as '3'
instead of '1'*.

For example, a player have 15 Dexterity (Attribute) and 10 in Dance
(Skill). They want to spend XP to bring Dance up to 20 (Desired Value).
They spend five points to bring the skill up to 15, then 5x3=15 to bring
the skill up to 20. From 1020 with 15 dexterity therefore costs 20 XP
Points.

My greatest problem with my own formula is when the Attribute (A) is
potentially lower than their Current Skill (B), Excel starts to count
negative points ending up in a greater cost than planned. I feel that
my lack of mathskill limit me from finding a way that counts negative
values as "0".


--
JemyM
------------------------------------------------------------------------
JemyM's Profile:
http://www.excelforum.com/member.php...o&userid=26945
View this thread: http://www.excelforum.com/showthread...hreadid=401682



  #3   Report Post  
Tushar Mehta
 
Posts: n/a
Default

Assuming the values you describe are in cells A1:D1, the formula you
want is:

=MAX(0,D1-MAX(A1,B1))*3+MAX(0,MIN(D1,A1)-B1)*1

The above has been lightly tested.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hi! I am new to the forum, and I love playing with Excel so I might be a
frequent visitor here, I dont know. :)

I have this nifty Excel problem that I cannot solve. I need a formula
that calculates the following for a roleplaying generator:

Value A: Attribute (that decides 1 point or 3 points)
Value B: Old Skill (current value)
Value C: XP Points (that must be spent)
Value D: New Skill (desired value)

Description: In an rpg we are playing you upgrade your Old Skill by
spending XP Points. As long as the skill is lower than your Attribute,
you pay 1 XP per Skill. When you go above your attribute you pay 3 XP
per skillpoint.

I want a formula that allow a player to write his wanted new skillevel
(D) in a cell.

The stylesheet will look at her old skillvalue (B) to determine if the
new value is greater than her old one. If it is, it will determine how
many points must be spent to reach the New Skill and show the result.
The issue is, *that each point above her Attribute (A) is count as '3'
instead of '1'*.

For example, a player have 15 Dexterity (Attribute) and 10 in Dance
(Skill). They want to spend XP to bring Dance up to 20 (Desired Value).
They spend five points to bring the skill up to 15, then 5x3=15 to bring
the skill up to 20. From 1020 with 15 dexterity therefore costs 20 XP
Points.

My greatest problem with my own formula is when the Attribute (A) is
potentially lower than their Current Skill (B), Excel starts to count
negative points ending up in a greater cost than planned. I feel that
my lack of mathskill limit me from finding a way that counts negative
values as "0".


--
JemyM
------------------------------------------------------------------------
JemyM's Profile:
http://www.excelforum.com/member.php...o&userid=26945
View this thread: http://www.excelforum.com/showthread...hreadid=401682


  #4   Report Post  
JemyM
 
Posts: n/a
Default


Ok, I try.

Lets say I have theese attributes):
Dexterity: 15
Intelligence: 18
Perception: 10

Theese values are present in the stylesheet.

I have theese skills:
Dance (Dexterity): 10
Science (Intelligence): 15
Search (Perception): 28

Theese values are also present in the stylesheet.
Within () you can read the attribute associated with each skill.

I now want to increase each skill 5 steps

Dance 15
Science: 20
Search: 33

I want to type that in a Cell, and then get how much xp I must spend
using the following rule:

Below the ATTRIBUTE a SKILL cost 1 XP to increase +1.
Above the ATTRIBUTE a SKILL cost 3 XP to increase +1.

And now to the examples:
Dance cost me 5 points. 10-15. Since I have 15 in the attribute
(Dexterity) I only need to pay 1 point per +1. If I type 15, the
stylesheet should say "5".

Science is a bit more difficult. Each step between 15-18 is 1 each, but
becouse the attribute (Intelligence) is 18, I must pay 3 points for my
final two steps up to 20.
15-18=3 points
19-20=6 points
= 9 points
If I type 20, the stylesheet should say "9".

Finally we have the exceptional search skill. The skill here is almost
tripple the attribute (perception), but still we should only need to
spend 3 points per step.
28-33=5*3=15 points
If I type 33, the stylesheet should say "15".

Barb Reinhardt Wrote:
I'm having some difficulty wrapping my brain around what you want. Can
you
give a table with specific examples of what you expect? Give me the
following examples:

New skill old skill
New skill = old skill
New skill < old skill

Also show the variation of what can happen for attributes as well.



--
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945
View this thread: http://www.excelforum.com/showthread...hreadid=401682

  #5   Report Post  
JemyM
 
Posts: n/a
Default


Tushar Mehta Wrote:
Assuming the values you describe are in cells A1:D1, the formula you
want is:

=MAX(0,D1-MAX(A1,B1))*3+MAX(0,MIN(D1,A1)-B1)*1

The above has been lightly tested.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions


I missed your message... :(

Although when I saw it I tried your formula and it worked flawlessly
:D

Thanks alot! :D


--
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945
View this thread: http://www.excelforum.com/showthread...hreadid=401682



  #6   Report Post  
Tushar Mehta
 
Posts: n/a
Default

You are welcome. Glad it worked out.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
{snip}

I missed your message... :(

Although when I saw it I tried your formula and it worked flawlessly
:D

Thanks alot! :D


--
JemyM

{snip}
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"