Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
xmaveric
 
Posts: n/a
Default Conform a total to a list of results?


This spreadsheet is used in a game to calculate the difficulty of an
encounter to a group of adventurers.

Basicly, the user will put in the number of monsters at each Challenge
Code, and then it should spit out a CC for the entire encounter. There
is no mathmatical forumla presented in the rules for determining the
CC. Here is what they give you:

"1. Doubling the number of obstacles of similar challenge codes
increases the Challenge Code of the encounter by one letter. Thus, two
Challenge Code "A" obstacles are basically the same as a single
Challenge Code "B" obstacle

2. Combining two obstacles whos Challenge Codes are one letter apart
increases the tougher obstacle by one letter; the tougher obstacle's
new Challenge Code determines the Challenge Code for the encounter. For
example, combiding a Challenge Code "C" obstacle and a Challenge Code
"D" obstacle creates a Challenge Code "E" encounter.

3. When combining two obstacles whos Challenge Codes are more than one
letter apart, the tougher obstacle's Challenge Code determines the
Challenge Code for the whole encounter. For examble, a Challenge Code
"A" obstacle coupled with a Challenge Code "D" obstacle is treated as a
Challenge Code "D" encounter. (The lesser obstacle does not contribute
significantly to the difficulty of the encounter.)

4. When combining three or more obstacles with different Challenge
Codes, group the obstacles in pairs from least difficult to most
difficult, use the guidelines above to combine each pair into a single
Challenge Code, and then repeat the pairing process. For example, three
obstacles of Challenge Code "B", "C", and "C" would be paired as
follows: "B"+"C" and "C" (unpaired). The "B" and "C" combine to form a
single Challenge Code "D" encounter, which then combines with the
second Challenge Code "C" encounter to yield a final encounter of
Challenge Code "E"."

As you can see on the attached sheet, the forumula I have works in most
cases, but not all. What I need is to know how to make the total value
snap to one of my pre-existing values (1,2,4,8,16,32,64,128,640,etc). I
need to to snap to the value closest, instead of using the lowest (as it
does now.)

This is the scenario I've been using to test it: 8B's 4D's 4F's should
come out to an "I" but instead yields and "H". I think this bug would
be fixed if the value in the Total column would snap to the closest
number in the "Numerical Value" row, but this is far beyond the extent
of my Excel knowledge.

If anyone can help with this I would greatly appreciate it.

Here is the spreadsheet for your viewing pleasu
http://www.xmaveric.com/exp.xls


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

  #2   Report Post  
Jim Rech
 
Posts: n/a
Default

I didn't read through all the rules for determining the right letter, but I
did look at your sheet. If the result you want is to find the letter in row
3 corresponding to the number in row 4 that is closest to the number in O6
you might try this formula in P6:

=IF(O6=0,"",CHAR(65+ROUND(LOG(O6,2),0)))

--
Jim
"xmaveric" wrote in
message ...

This spreadsheet is used in a game to calculate the difficulty of an
encounter to a group of adventurers.

Basicly, the user will put in the number of monsters at each Challenge
Code, and then it should spit out a CC for the entire encounter. There
is no mathmatical forumla presented in the rules for determining the
CC. Here is what they give you:

"1. Doubling the number of obstacles of similar challenge codes
increases the Challenge Code of the encounter by one letter. Thus, two
Challenge Code "A" obstacles are basically the same as a single
Challenge Code "B" obstacle

2. Combining two obstacles whos Challenge Codes are one letter apart
increases the tougher obstacle by one letter; the tougher obstacle's
new Challenge Code determines the Challenge Code for the encounter. For
example, combiding a Challenge Code "C" obstacle and a Challenge Code
"D" obstacle creates a Challenge Code "E" encounter.

3. When combining two obstacles whos Challenge Codes are more than one
letter apart, the tougher obstacle's Challenge Code determines the
Challenge Code for the whole encounter. For examble, a Challenge Code
"A" obstacle coupled with a Challenge Code "D" obstacle is treated as a
Challenge Code "D" encounter. (The lesser obstacle does not contribute
significantly to the difficulty of the encounter.)

4. When combining three or more obstacles with different Challenge
Codes, group the obstacles in pairs from least difficult to most
difficult, use the guidelines above to combine each pair into a single
Challenge Code, and then repeat the pairing process. For example, three
obstacles of Challenge Code "B", "C", and "C" would be paired as
follows: "B"+"C" and "C" (unpaired). The "B" and "C" combine to form a
single Challenge Code "D" encounter, which then combines with the
second Challenge Code "C" encounter to yield a final encounter of
Challenge Code "E"."

As you can see on the attached sheet, the forumula I have works in most
cases, but not all. What I need is to know how to make the total value
snap to one of my pre-existing values (1,2,4,8,16,32,64,128,640,etc). I
need to to snap to the value closest, instead of using the lowest (as it
does now.)

This is the scenario I've been using to test it: 8B's 4D's 4F's should
come out to an "I" but instead yields and "H". I think this bug would
be fixed if the value in the Total column would snap to the closest
number in the "Numerical Value" row, but this is far beyond the extent
of my Excel knowledge.

If anyone can help with this I would greatly appreciate it.

Here is the spreadsheet for your viewing pleasu
http://www.xmaveric.com/exp.xls


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



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


Similar Threads
Thread Thread Starter Forum Replies Last Post
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
Drop down List problems LHearn Excel Discussion (Misc queries) 2 June 20th 05 10:37 PM
Is there a way to create a drop-down list of links in Excel? t_boden Excel Worksheet Functions 1 February 3rd 05 07:14 PM
how do I add a list of numbers to get a total bibs Excel Discussion (Misc queries) 2 November 27th 04 04:31 AM
Counting Repeated text or duplicates in a list Repeatdude Excel Discussion (Misc queries) 5 November 26th 04 08:10 PM


All times are GMT +1. The time now is 11:35 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"