Help with formula in Word or Excel

[Nellisir]

Disclaimer: I'm not a math or computer guy. I had a high school class on programming in Basic 13 years ago. ;-)

I want to create a formula in Word or Excel that looks at the result in 1 cell and returns a value; I don't know how to make it conditional multiple times.

Specifically, I want to enter a creature CR in one cell, and have the XP (of an EL = to the CR) automatically generated in another. I can do 1=300, what I can't do is 1=300, 2=600, 3=900, and so forth.

Help...
Nell.

 

[Firzair]

Disclaimer: I'm not a math or computer guy. I had a high school class on programming in Basic 13 years ago. ;-)

I want to create a formula in Word or Excel that looks at the result in 1 cell and returns a value; I don't know how to make it conditional multiple times.

Specifically, I want to enter a creature CR in one cell, and have the XP (of an EL = to the CR) automatically generated in another. I can do 1=300, what I can't do is 1=300, 2=600, 3=900, and so forth.

Help...
Nell.

HI,
looks like you just need to enter the Fomula =CR-Cell*300, i.e. if you enter the CR in Cell A4 the Formula goes =A4*300.
If you need some more conditions, you need multiple IF-Functions (I think there are IF-Functions, in our German-versions there are WENN-Functions), the function should be like this =IF(A4=1;300;IF(A4=2;600;IF(...)))

Hope that helps

Greetings
Firzair

 

[Nellisir]

HI,
looks like you just need to enter the Fomula =CR-Cell*300, i.e. if you enter the CR in Cell A4 the Formula goes =A4*300.
If you need some more conditions, you need multiple IF-Functions (I think there are IF-Functions, in our German-versions there are WENN-Functions), the function should be like this =IF(A4=1;300;IF(A4=2;600;IF(...)))

Duh. How much easier is that.


Nell.

Who gets what he deserves for trying to be logical at 11pm.

 

[Silveras]

More complicated

It is slightly more complex than that. Remember that XP awards change with level. If you embed the values in the forumla, you will have to change the formulas every time you need to award XP for a different level. In 3.5 (or 3.0 FR), a party of four characters levels 4/5/5/6 could need 3 different values for CRs above 1 because each character earns XP based on his/her own level (not the group average, as was 3.0 originally).

You are better off looking at the LOOKUP, VLOOKUP, and HLOOKUP functions, and using lookup tables to store the values you need. Something like =HLOOKUP("CR"+Cstr([CRCell]), XPTableSheet!A1:T21, [LevelCell]+1, TRUE)

assuming that
there is a sheet named XPTableSheet
row one of that sheet has values of "CR1", "CR2", "CR3", etc. in columns A-T
rows 2 through 21 have the XP values for each CR at that level-1 going across

The HLOOKUP function says: Look across the top row of the range XPSheetTable!A1:T21, and find the column that reads "CRx". Go down that column to row Level+1, and return the value there. The TRUE part means you want it to find an exact match, and not to approximate.

Hope that helps

 

[DaveStebbins]

Silveras has it right. The problem with brute forcing it by nesting IF statements is that (last time I checked) you can only nest a max of seven IF statements. A LOOKUP function is what you need. I happen to prefer VLOOKUP instead of HLOOKUP, for no reason other than that's how I'm used to looking at data. Also, with VLOOKUP, you can see more of the table (when it runs top-to-bottom) than with HLOOKUP (where the table runs left-to-right).

If you need more specifics on how to set things up, repost to this thread and we'll walk you through it slower. Excel help is OK sometimes, but I've never seen it be better than a co-worker or friend who knows what they're doing.

-Dave

 

[Silveras]

Mixing Word and Excel

Nellisir - By the way, you will note that I did not mention Word at all. That's because Word has very limited table/cell functions. Excel has everything more complicated than AVERAGE, really.

DaveStebbins - Yeah, well, VLOOKUP and HLOOKUP are really a matter of orientation Since the XP Awards table is 2-dimensional, you could use either, or both, to access the information equally well. VLOOKUP to find the character's level then return the value over in the appropriate CR cell, or HLOOKUP to find the CR column then return the value in the appropriate level's row.

 

[Heretic Apostate]

Silveras, are you sure about the TRUE part? If I recall correctly (and I am sure I do ), if the last space is marked TRUE or omitted altogether, you get either the number you selected, or the highest number below it, or an error function. With FALSE, it'll get either the number you select, or an error function.

For instance, with a table of:
1, Apple
5, Orange
10, Grapes

If you look up 6 with TRUE, you'll get Orange. If you look it up with FALSE, you'll get an error statement.

What's the CStr function, anyway? Is it a way of converting the number to text? I would have used the TEXT([CR cell],"#") function. Though that would have required a concantate (sp?) of "CR"&TEXT([CR Cell],"#"). Does adding work with text?

Dang, I miss digging into Excel. I think the time is coming where I'll have to break out the old books and dig some more.

VLOOKUP, SUMIF, and a few others are my most used formulas within Excel... I'm trying to branch out, but these are the classics... Though I'd LOVE to see those German functions. Are they in XP?

Edit: Oh, yeah, another difference between TRUE and FALSE. With TRUE, the reference numbers (1, 5, 10 in my example) have to be in ascending order. If it were 1, 10, 5, instead, searching for 6 would have returned Apple instead (I think). Basically, TRUE down the list until it reaches a point where it has either reached or passed the reference it's looking for.

With FALSE, because it's looking for exactly that reference, it doesn't have to be in order. I think it only picks up the first occurrence, however...

 

[Silveras]

D'oh !

Heretic Apostate - You are correct. I described the TRUE and FALSE options in reverse. In this case, if the table is constructed to essentially copy the one in the DMG, it does not affect the operation (unless you use very low CRs against high level opponents, or very high CRs against low level). Also, you are correct that using TRUE, the values must be in ascending order.

In building a test version, I found it more convenient to eliminate the text conversion and concatenation issues -- just put the number of the CR in the first row without "CR".

The '+' operator was the original concatenation operator in Visual Basic for a long time. The '&' was made the concatenation operator in VB 3.0, but old habits die hard sometimes (and MS preserves backward compatability forever when they can).

 

[Nellisir]

Honestly, I was just looking for the answer to an idiot question (generate XP for an encounter with a EL equal to a monster's CR for characters of equal level to the EL). As Firzair pointed out, (and I was too sleepy to figure out) XP=CR*300.

HOWEVER, this is cool stuff, and I'm going to figure it out and see what more I can do with it.

Cheers!
Nell.