Software

Ask something
Discussion Locked

Excel 2000

0 helpful
4 replies

by michael34 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Greetings,

My question is this, I am good with excel but I'm unsure what the max lenght of a formula is which is why I don't think this next thing can be done. Right now I have a friend who wants me to program a character sheet for a RPG he started. He wants the EXP for the next level to automatically change once the experience the character already has moves up to the the next level. I figured out a formula to do this however he has about 200 level's I need to imput so before I do this I need to know if Excel would support it.

The current formula I use is this:
=IF(F5<500,"500", IF(F5<1000,"1000",IF (F5<1500,"1500",IF(F5<2000,"2000",IF(F5<2000,"2000")))))

Its basic. Right now F5 is the value of exp the character has earned, F6 I have another formula that subtracts F5 from F7, and in F7 I have that huge formula above.

So it looks like this
Experience Earned (F5): 99
Experiennce Remaining (F6): 01
Next Level (F7): 100

Is there a way I can use the formula I put above for F7 and extend it to so it will use 200 different IF conditions or is this even supported or is there an easer way to automate this?

Thanks for the help

4 replies

by dklippert

Use VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
The function will use a lookup_value (F5) to find a match in a table_array (a table located on the worksheet or another locations).
The function looks in col_index_num (column number. If the values 1 to 1000 are in the first column, the corresponding value might be in column 2).
The final parameter, range_lookup,
"Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned."
Name the range that makes up the table.
=VLOOKUP(F5,NamedTable,2,TRUE)
Copy down the column.

by michael34

Thanks for the help however I decited to use the IF value on another worksheet to grab values such as IF it was greater than lets say 7 to go down to the next formula so basically it referrs to another formula until it finds the number range it is after. Took a long time to program but it works.

This discussion is closed to new replies.