Excel questions

[Nellisir]

I'm working on encounter tables in Excel, and it's actually going quite well (thanks to an initial equation from Eric Noah). The more I add, the more I think of adding, though, and the more questions I run into.

• Can Excel generate or select a random number, or one in a range? (1-100, or 2d4+3, for example)

• Can you arrange a formula to only use visible rows (ie, if you hide all creatures of CR 8 or greater) What about visible rows but hidden columns? (right now I have some behind-the-scenes stuff in hidden columns)

• I might be able to figure this out later, but an answer now would save time...
  This equation gives the EL of an enounter:

  =IF(J3=1,L3,IF(J3=2,L3+2,IF(J3=3,L3+3,IF(J3=4,L3+4,IF(J3=5,L3+5,IF(J3=6,L3+5,IF(J3=7,L3+6)))))))

  where L3 is the CR of the creature and J3 is the number of creatures. At higher numbers of creatures (5-6, etc) the EL stays the same, so it should look something like

  IF(J3=5 OR J3=6,L3+5

  where bold is what I'd like to add. How would that be written?

Thanks!

 

[Thanee]

• Can Excel generate or select a random number, or one in a range? (1-100, or 2d4+3, for example)

Yeah. RAND()*(b-a)+a generates a random real number between a and b.

• Can you arrange a formula to only use visible rows (ie, if you hide all creatures of CR 8 or greater) What about visible rows but hidden columns? (right now I have some behind-the-scenes stuff in hidden columns)

No clue.

• At higher numbers of creatures (5-6, etc) the EL stays the same, so it should look something like

  IF(J3=5 OR J3=6,L3+5

  where bold is what I'd like to add. How would that be written?

Should be IF(OR((J3=5),(J3=6,L3+5))).

Bye
Thanee

P.S. Excel actually has a help function, yaknow.

 

[Rodrigo Istalindir]

1. Yes. RANDBETWEEN(x,y). You may have to tell it to load the Analysis pack (I think its called) depending on your version of Excel to get the random functions.

2. I'm not sure what you mean -- a formula will contain a cell reference, which doesn't care if the cell is visible or not. Frequently, you'll use a whole seperate sheet for the lists, and just use the lookup functions to return a specific subset to the main sheet.

3. You have to do nested 'IF's. It can get messy. For example,

if (j3=5,l3+5,if(j3=6,l3+5,l3+4)

If j3 is 5 it finishes the calc. If its not, it checks to see if j3 is 6, if it is' it finishes the calc. If its not '6' either, then it falls to the final 'fail' calc.

 

[Nellisir]

P.S. Excel actually has a help function, yaknow.

This is quicker. The Help only helps if you know what you're asking for. Plus, I'm doing this in RAND()*(10-5)+5 minute intervals when my wife isn't looking.

Thanks!

 

[Rodrigo Istalindir]

I think Thanee has a typo. I think that version should be:

=IF(OR(J3=5,J3=6),L3+5,)

If it's 5 or 6, then the result is l3+5, else the result is 0 (or whatever you want after the last ',' such as l3+6).

There's probably a more elegant way of doing that function, though. If I have time, I'll much about with it later.

 

[Thanee]

The second part after the comma is the THEN? Oh, ok, yeah, then what I have written above isn't quite right, of course. Don't actually use Excel for that stuff, I only looked into the help file.

Anyways... OR(A,B) is what you need.

Bye
Thanee

 

[Nellisir]

I think Thanee has a typo. I think that version should be:



If it's 5 or 6, then the result is l3+5, else the result is 0 (or whatever you want after the last ',' such as l3+6).

There's probably a more elegant way of doing that function, though. If I have time, I'll much about with it later.

A more elegant way would be nice, but I got this to work:

=IF(J34=1,L34,IF(J34=2,L34+2,IF(J34=3,L34+3,IF(J34=4,L34+4,IF(OR(J34=5,J34=6),L34+5,IF(OR(J34=7,J34=8,J34=9,J34=10),L34+6))))))

(L34 is the CR, J34 is the number of creatures). I think I can add one more set in there.

I got Thanee's RAND to work, but I probably will have to move the hidden columns to another sheet - the EL calculator apparently doesn't like if if the number encountered is =RAND instead of a solid number, which means another hidden column.

This is going to be pretty cool. Now that I can do random numbers, I can do treasure.

Fun project to learn Excel with, that's for sure.

 

[Nellisir]

I'll post the sheet here later today, once I tweak it a little more.

 

[Rodrigo Istalindir]

Try this:

=IF(J34=1,L34,IF(J34<5,L34+J34,IF(J34<7,L34+5,IF(J34<11,L34+6,"too many critters"))))

 

[Ferghis]

Can Excel generate or select a random number, or one in a range? (1-100, or 2d4+3, for example)

Thanee's RAND is better than RANDBETWEEN because many machines haven't downloaded the component necessary for the latter to work.

Can you arrange a formula to only use visible rows (ie, if you hide all creatures of CR 8 or greater) What about visible rows but hidden columns? (right now I have some behind-the-scenes stuff in hidden columns)

This is a little more complicated, at least for someone with my preparation. I'd need a little more detail to propose a solution, if no-one else hasn't already done so. I would suggest putting the behind the scenes stuff in a separate sheet so that you don't have to hide rows or columns.