Ok, what I am doing is trying to create an excel sheet that will tell
the experience given per encounter in our RPG game. The game gives
every monster a challenge code. It then has a table that says how
difficult each challenge code should be to a group, depending on the
group's average level.

So the Challenge Rating table is something like this:

Level Easy Challenging Extreme
1 A B C
2 A B C
3 B C D
4 B C D
5 B C D
6 C D E
7 C D E
8 C D E
9 D E F
10 D E F
11 D E F
12 E F G
13 E F G
14 E F G
15 F G H
16 F G H
17 F G H
18 G H I
19 G H I
20 G H I

And the EXP table is like this:

Level Easy Challenging Extreme
1 100 300 400
2 200 600 800
3 300 900 1200
etc....



So the first sheet is layed out like this:

Group Level:
Challenge Code of Encounter:
EXP awarded to group:

I want the EXP number to be filled in automatically when the user
inputs the group level and Challenge Code of the encounter.

Is there any way to do this?


--
xmaveric
------------------------------------------------------------------------
xmaveric's Profile: http://www.excelforum.com/member.php...o&userid=26440
View this thread: http://www.excelforum.com/showthread...hreadid=397117

sure
=VLOOKUP(level,challenge table,MATCH(challenge rating,challenge
table,0),FALSE)
will find your rating......abc etc
=vlookup(level,exp table,match(challenge rating,exp table,0),false)
will find your experience points
or have i missed something?


paul
remove nospam for email addy!



"xmaveric" wrote:


Ok, what I am doing is trying to create an excel sheet that will tell
the experience given per encounter in our RPG game. The game gives
every monster a challenge code. It then has a table that says how
difficult each challenge code should be to a group, depending on the
group's average level.

So the Challenge Rating table is something like this:

Level Easy Challenging Extreme
1 A B C
2 A B C
3 B C D
4 B C D
5 B C D
6 C D E
7 C D E
8 C D E
9 D E F
10 D E F
11 D E F
12 E F G
13 E F G
14 E F G
15 F G H
16 F G H
17 F G H
18 G H I
19 G H I
20 G H I

And the EXP table is like this:

Level Easy Challenging Extreme
1 100 300 400
2 200 600 800
3 300 900 1200
etc....



So the first sheet is layed out like this:

Group Level:
Challenge Code of Encounter:
EXP awarded to group:

I want the EXP number to be filled in automatically when the user
inputs the group level and Challenge Code of the encounter.

Is there any way to do this?


--
xmaveric
------------------------------------------------------------------------
xmaveric's Profile: http://www.excelforum.com/member.php...o&userid=26440
View this thread: http://www.excelforum.com/showthread...hreadid=397117

Hi,

I am a little confused over the relationship between to 2 tables. How
does a challenge code of A relate to an Experience award?

Anyway, I hope the following provides a clue as to an answer to the
question.

Set up a sheet to allow entry of the group's level, say in cell E1 and
set the value to 1.

Set-up the Challenge level in cell E2 and set the value to "Easy".

Use DataValidation to ensure that values are between 1 and 20 for
group or are only "easy", "Challenging" or "Extreme" for challenge
level.

Press ALT+11 and insert a new module.

In the new module copy in the following code...

Code:
--------------------

Function AwardXP(pintLevel As Integer, _
pstrChallenge As String)

' Custom Function to return Experience for an encounter
'
' Inputs
' pintLevel Integer Level of group
' pstrChallenge String Level of encounter (Easy, Challenging, or Extreme)
'
' Output
' AwardXP Integer Experience for encounter

'Declarations
Dim intChallenge

'Convert Challenge string in to a number
Select Case pstrChallenge
Case "Easy"
intChallenge = 1
Case "Challenging"
intChallenge = 3
Case "Extreme"
intChallenge = 4
End Select

'Calculate experience
AwardXP = (pintLevel * 100) * intChallenge

End Function

--------------------


Press Alt+F11 again.

In cell E3 enter the following formula

Code:
--------------------

=AwardXP(E1, E2)

--------------------


Cell E3 should now show 100.

How this works...

Each challenge entry for cell E2 is converted into a number under
"Convert Challenge string in to a number". Data validation ensures only
3 challenge types are possible. Hence "Easy" = 1, "Challenging" = 3 and
"Extreme" = 4. You can easily amend these values or add different
challenge codes.

I have set the formula in the code under "Calculate experience" as:
Group's level * 100 * Challenge Code.

Easy (cell E2) has a challenge code of 1, and the Group level (cell E1)
= 1, thus:
Group's level * 100 * Challenge Code becomes
1 * 100 * 1 = 100

If you amend cell E1 to 9 and cell E2 to "Extreme" you get:
9 * 100 * 4 = 3600

Hopefully using the above you can amend the forumla to suit yourself.

One tip.

If you set up a column containing 1 through 20 in different cells, and
another column containing Easy, Challenging and Extreme, you can use
DataValidation, select list and ensure in cell dropdown is checked. In
cell E1 do this using the column of numbers as the source and for cell
E2 the column of Easy, etc as the source. You'll then get dropdown
boxes in the cells that a) save typing and b) ensure that only valid
entries can be set.

HTH

Art


--
HiArt
------------------------------------------------------------------------
HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953
View this thread: http://www.excelforum.com/showthread...hreadid=397117