Can Excel do this?

[xmaveric]

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?

[HiArt]

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 Data>Validation 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...

Please Login or Register  to view this content.

Press Alt+F11 again.

In cell E3 enter the following formula

Please Login or Register  to view this content.

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 Data>Validation, 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

[paul]

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