Excel programming

Have a computer problem? Want to show off your rig? Lets hear it!

Moderators: verdilak, hippie_mama

Excel programming

PostPosted by Rayven » Mon Mar 22, 2010 7:03 pm

I have a really nerdy question.

I am trying to code an Excel spreadsheet for a Rogue Trader character sheet, and I want to have the Rank (level) automatically increase when the requisite XP is hit. I know how to write an =IF statement, but can't figure out how to get it to work with ranges.

Example: If I type
Code: Select all
=IF(C32<7000,1,"")
it will display a 1 in the Rank field provided the character has fewer than 7000 XP. Now, the XP range for Rank 2 is 7000 thru 9999. How do I get Excel to recognize that range? I have tried
Code: Select all
=IF(7000<C32<10000,2,"")
and that did not work. Neither did
Code: Select all
=IF(C32=7000:9999,2,"")
. I also tried having a worksheet called Code, with Column A having the numbers 7000 through 9999 in it, and typing
Code: Select all
=IF(C32='Code'!A1:A3000,2,"")
and got #VALUE as my result no matter what number I put in C32.

Can any of you help me out?

*note: C32 is the cell that the XP is in, in case you hadn't realized.
STARE INTO MY EYES FOREVER
LET ME TAKE YOU IN MY ARMS
DANCE WITH ME OUT IN THE GARDEN
WHERE TOMORROW NEVER COMES
FOLLOW ME INTO THE DARKNESS
WHERE THE NIGHTMARES NEVER FADE
WHISPER DREAMS MY FALLEN ANGEL
FROM WHICH YOU WILL NEVER WAKE
Religion starts wars. Spirituality ends them

Image
User avatar
Rayven
Not a Platypus
Not a Platypus
 
Posts: 1350
Joined: Wed Nov 26, 2008 2:06 pm
Location: Cubicle greyness...I'm surrounded by false intelligence!
Favorite System: Any

Re: Excel programming

PostPosted by NulSyn » Mon Mar 22, 2010 8:11 pm

Is it space sensitive in Excel?

Code: Select all
=IF(C32 < 7000, "1", "")

and
Code: Select all
=IF(7000 < C32 < 10000, "2", "")


I would have thought would work, but notice the spaces, if its space sensitive yours are missing. I only had a basic course on Excel so I am not entirely sure.
User avatar
NulSyn
Forum Hippo
Forum Hippo
 
Posts: 2896
Joined: Sun Jul 27, 2008 11:02 am
Location: Loganville,GA

Re: Excel programming

PostPosted by NulSyn » Mon Mar 22, 2010 8:14 pm

EDITED to fix code
Oh wait it needs boolean statements
Code: Select all
=IF((AND(C32 < 10000,  C32 > 7000)), "2", "")

Maybe try that?
User avatar
NulSyn
Forum Hippo
Forum Hippo
 
Posts: 2896
Joined: Sun Jul 27, 2008 11:02 am
Location: Loganville,GA

Re: Excel programming

PostPosted by Rayven » Mon Mar 22, 2010 8:34 pm

That worked, but it also removed my style editing (which is no big deal). Also, Excel flips me the bird when I put spaces in my code.
STARE INTO MY EYES FOREVER
LET ME TAKE YOU IN MY ARMS
DANCE WITH ME OUT IN THE GARDEN
WHERE TOMORROW NEVER COMES
FOLLOW ME INTO THE DARKNESS
WHERE THE NIGHTMARES NEVER FADE
WHISPER DREAMS MY FALLEN ANGEL
FROM WHICH YOU WILL NEVER WAKE
Religion starts wars. Spirituality ends them

Image
User avatar
Rayven
Not a Platypus
Not a Platypus
 
Posts: 1350
Joined: Wed Nov 26, 2008 2:06 pm
Location: Cubicle greyness...I'm surrounded by false intelligence!
Favorite System: Any

Re: Excel programming

PostPosted by NulSyn » Mon Mar 22, 2010 8:38 pm

Rayven wrote:That worked, but it also removed my style editing (which is no big deal). Also, Excel flips me the bird when I put spaces in my code.

Now what takes some thinking is stringing IF statements so that you can show different ranks all in the same field.

As a template something like:
Code: Select all
=IF((AND(D2>5,C2>10000)),3,(IF((OR(D2>5,C2>10000)),2,1)))
User avatar
NulSyn
Forum Hippo
Forum Hippo
 
Posts: 2896
Joined: Sun Jul 27, 2008 11:02 am
Location: Loganville,GA

Re: Excel programming

PostPosted by Rayven » Mon Mar 22, 2010 8:56 pm

Yeah. I have decided that parenthetical endings are the bane of my existence because of this:
Code: Select all
=IF(C32<7000,1,(IF((AND(C32<10000,C32>6999)),2,(IF((AND(C32<13000,C32>9999)),3,"")))))


It works, and displays right...just annoying keeping up with closures.

When I'm done, size restrictions allowing, I'll post the file for you to look over if you want.
STARE INTO MY EYES FOREVER
LET ME TAKE YOU IN MY ARMS
DANCE WITH ME OUT IN THE GARDEN
WHERE TOMORROW NEVER COMES
FOLLOW ME INTO THE DARKNESS
WHERE THE NIGHTMARES NEVER FADE
WHISPER DREAMS MY FALLEN ANGEL
FROM WHICH YOU WILL NEVER WAKE
Religion starts wars. Spirituality ends them

Image
User avatar
Rayven
Not a Platypus
Not a Platypus
 
Posts: 1350
Joined: Wed Nov 26, 2008 2:06 pm
Location: Cubicle greyness...I'm surrounded by false intelligence!
Favorite System: Any

Re: Excel programming

PostPosted by NulSyn » Mon Mar 22, 2010 9:03 pm

Rayven wrote:
When I'm done, size restrictions allowing, I'll post the file for you to look over if you want.

Sure, I wouldn't mind a looksie.
User avatar
NulSyn
Forum Hippo
Forum Hippo
 
Posts: 2896
Joined: Sun Jul 27, 2008 11:02 am
Location: Loganville,GA


Return to Computers!

Who is online

Users browsing this forum: No registered users and 0 guests

cron