Using a range of cells or numbers

Question

Level 1

8 points

Using a range of cells or numbers

I got frustrated working on the Palladium supernatural character stats setup, and figured the AD&D stat block may be simpler. More straight forward.

No.

I started with the Strength attribute. Here’s the table I worked up for that.

Same table, just pulled out of the book for contrast. As you can see, up until 18 it is in fact very straight forward. I was able to get the following to work just fine:

=IF($A$3,LOOKUP($A$3,Strength::A2:C11,Strength::D2:D11),"ERROR")

”ERROR” is there because I wasn’t sure if not having an “IF FALSE” reaction would gum up the works. However, if you roll an 18 for Strength there’s a chance to roll percentile dice to gain a higher strength score.

”…Furthermore, fighters with an 18strength are entitled to roll percentile dice in order to generate a random number between 01 and 00 (100) to determine exceptional strength; …” (TSR AD&D PH pg 9)

I’m trying to get it to display the EXCEPTIONAL STRENGTH bonuses if a second cell has a number in it. I’ve tried using LOOKUP, AND, OR and NOT. None worked.

By itself, this worked:=IF(B3<50,Strength::D12,"nil") but I can’t add it to the main cell. It also precludes the scores from 51 - 00.

As you can probably see, this one didn’t work at all: =IF(B3>50,LOOKUP(B3,Strength::A12:A15,OR(Strength::C12:C15),Strength::D13:D16),"nil")

I know I’ve already asked a lot here but I just can’t find anything online that has helped me figure this out for myself, and I’m still learning Numbers.

If you can help me out again, I’d appreciate it.

Posted on Aug 5, 2022 10:10 PM

Reply

Answer 1

Top-ranking reply

Algoroth Author

Level 1

8 points

Aug 6, 2022 12:07 PM in response to Algoroth

Reply

Answer 2

Badunit

Level 7

30,948 points

Aug 13, 2022 5:44 PM in response to Algoroth

Here is what I was talking about except I found I needed to divide the exceptional strength by 1000, not 100, because there was an 18 in the table without any exceptional strength and that would have been the same as 18.00 (18 with "100" exceptional strength).

Table 2 has a few example lookups.

Formula in Table 2 C2 =XLOOKUP(A2+B2÷1000,Table 1::A,Table 1::F,"",−1)

Table 1 columns B and C are for looks only, they are not used for anything else.

Exceptional strength is from 01-100. Do not use 0 for 100, use 100.

You could rename column A to "Strength.Exceptional" if that would make more sense.

Reply

Answer 3

Algoroth Author

Level 1

8 points

Aug 11, 2022 6:56 PM in response to Algoroth

Okay since my first request for help on this one, I’ve been able to get the follow to work:

IF($A$3<18,LOOKUP($A$3,Strength::$A$2:$C$10,Strength::$D$2:$D$10),

IF($A$3=18,LOOKUP($A$3,Strength::$A$11,Strength::$D$11),IF(AND(A3=18,B3≤50),Strength::D12,)))

A3 and B3 are on a separate sheet which I posted in response for clarity. So, on Strength scores from 3 to 18/50, it’s all sorted out. However, if you refer to the previous images posted, there are stats for scores of 18/51-18/75, 18/76-18/90, 18/91-18/99 and the infamous 18/00. The last (18/00) admittedly should also be fairly simple. It’s getting numbers to look for ranges (LOOK BETWEEN 76 and 90 for X number) that has me stumped.

Can anyone here help? There are no manuals that I’ve found for Numbers and a Titanic’s load for Excel.

Reply

Answer 4

Badunit

Level 7

30,948 points

Aug 12, 2022 6:17 PM in response to Algoroth

A few thoughts:

You are mixing your data. A column of data should be data about one thing. Your columns A-C go off the rails when you get past 18. The first rows are strength values, after 18 it becomes "exceptional strength" values. It is especially important to not mix data when you need to do a lookup on that data. There is also a problem with the way you have arranged the strength data across three columns vs it being in one.

I believe your LOOKUP function that searches columns A:C is really only looking in column A. If it works at all it is because it is finding a close match. The "search where" parameter needs to be one dimensional, such as column A only.

All your strength values need to be in column A, not A, B, and C. You can put the lowest strength of the row in A. A strength of 3 would get a 3 in column A. A strength of 4-5 would get a 4 in column A. Etc. No need for columns B and C except as eye candy. With XLOOKUP you can specify "exact match or next smallest" so if you do an XLOOKUP on a strength of 5, it will get data from the row that has the 4 (there is no 5 in column A, the next smallest is 4).

For strength of 18, you could use decimals for the "exceptional strength". Your XLOOKUP would be on the calculated value of strength + (exceptional strength)/100. Column A would have 18.01, 18.51,18.76, 18.91 and then 18.00. When looking up strengths of 3-17, set the "exceptional strength" to 0 or just use the strength by itself.

Reply

Answer 5

Algoroth Author

Level 1

8 points

Aug 6, 2022 12:08 PM in response to SGIII

Okay, lets try this like this. The following is the destination for the requested values.

If you look at the previous tables, <Sorry, had to post the table separately > you’ll see that column (ABC) 2 through 10 are a straight forward 3 - 18. The number to search for is placed in cell A3 on a separate sheet. However, (ABC) 11 begins the EXCEPTIONAL STRENGTH portion of the stat. Thi number is placed in cell B3.

Normal Strength is designated a number from 3 (you roll 3 6 sided dice to determine this number) to 18. If the player rolls an 18 for their Strength stat, they then roll a set of percentile (2 10 sided dice where one is numbered 1-10 and the other is numberd 10-00) to determine how Exceptional their strength is. These are then recorded as 18/25 or 18/42 or the infamous 18/00. But you can’t use this notation method so, I placed the standard stat in one cell (A3) and the exceptional stat in another (B3).

Now. In the first tables provided, a series of bonuses correspond to each score. I want to have each bonus be placed into a cooresponding cell next to the rolls (A/B3). This is working quite well for cells C,D,E & G 3 as you can see. However, the bonuses continue with the exceptional stat roll. Returning to the previous examples we have:

18/25 | 1 | 3 | 1000 | 1-3 | 20%

18/42 would be the same as 18/25 as it is under 50%.

18/00 | 3 | 6 | 3000 | 1-5 | 40%

I can get these to display properly up till 18, but once I try to get it to display the exceptional strength stats, I get all sorts of errors. I’m sure that it’s something so stupidly simply that once pointed out, I’ll kick myself for not seeing it. However since I’m no expert with Numbers, there’s no manual I can get my hands on, and Apple absolutely ***** in the online help department, I come here asking for help rather than saying to **** with it and deleting the app.

Reply

Answer 6

SGIII

Level 8

37,580 points

Aug 5, 2022 11:02 PM in response to Algoroth

I have trouble following your example, and don't see where you are trying to enter your formulas.

However, one piece of advice. Don't use LOOKUP. That's a very old function, kept for backwards compatibility. It waslong ago replaced by VLOOKUP and, better yet, the more modern and flexible XLOOKUP.

SG

Reply

Answer 7

SGIII

Level 8

37,580 points

Aug 12, 2022 12:12 AM in response to Algoroth

I confess I really don't understand what you are trying to do. I know nothing about Palladium or character stats or strength attributes.

One piece of advice, though. Do not use LOOKUP. That is a ancient function from the dawn of spreadsheets. The more modern and far more flexible XLOOKUP may be what you want. But again, what are you trying to do? A simple example, including the results you want, might help.

SG

Reply

Answer 8

Aug 12, 2022 7:26 AM in response to Algoroth

Without fully understanding your problem I can see your troubleshooting is effective when you try simpler formulae first such as

“By itself, this worked:=IF(B3<50,Strength::D12,"nil") but I can’t add it to the main cell.”

and making sure to use cells outside the data range to prevent recursive error.

Did you notice your D4 value of 1 does not match the book’s value of -1?

I’m guessing the bin frequency formula of excell would be useful here if Numbers does not already have it.

Reply

Answer 9

Algoroth Author

Level 1

8 points

Aug 12, 2022 12:00 PM in response to EEcontrols

First, thank you. It seemed the most logical method.

Second, again thank you. No I hadn’t noticed the error. I fixed it.

Finally, you are the third person to say that you didn’t understand what I’m trying to do. I don’t know what is tripping everyone up. I’ll try again.

The Strength Stat or Attribute in Advanced Dungeons and Dragons is determined by rolling 3 6 sided dice. If you roll 3 through 17 then your character has normal strength. If however, you roll an 18 for your strength stat, then they player rolls 2 10 sided die. One die represents the tens place, the other the ones place, giving you a percentage from 01 to 00. This percentage roll, added to the 18 gives you a stat like 18/56. This is called Exceptional Strength.

The exceptional strength stats go above the standard 18 in 5 ranges.

01 to 50

51 to 75

76 to 90

91 to 99

00

Each range has a set of bonuses that accompany them, as detailed above. What I am trying to do, is create a formula that, if the strength stat is between 3 and 17, it populates a set of cells adjacent to the strength stat corresponding to that number.

if however the stat is 18, the formula would check the cell next to the strength stat for the percentage given. Say for instance, the afore mentioned 56%. How do I tell Numbers to look for where 56% would fit, inside the 5 ranges above to populate the adjacent cells with the proper values?

Reply

Answer 10

Aug 12, 2022 2:50 PM in response to Algoroth

Thanks for clarifying. While I do not have the complete answer, perhaps this partial answer will help find the range using the AND function for comparing the values (row four) with your ranges in first two columns (A and B).

Reply

Answer 11

Algoroth Author

Level 1

8 points

Aug 13, 2022 7:10 PM in response to Badunit

Thank you, that actually helped a lot. It also let me figure out the rest of the stat block.

And, can someone explain to me why I have to sign in 10 (+/-) times before it accepts my post? It keeps telling me that my session has expired and that I must sign back in.

Reply

Answer 12

Algoroth Author

Level 1

8 points

Aug 12, 2022 11:43 AM in response to SGIII

Yes, I’ve tried to use XLOOKUP:

XLOOKUP(B3,Strength::A12:C16,Strength::D12,"00",1,search-type)

It tells me “Argument 3 of XLOOKUP is invalid”.

Reply

Using a range of cells or numbers

Similar questions