General discussion


Excel 2000

By Michael34 ·

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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Excel 2000

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.
Copy down the column.

Collapse -

by Michael34 In reply to

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.

Collapse -

by Michael34 In reply to Excel 2000

This question was closed by the author

Collapse -


by uslogical In reply to Excel 2000


I thought you are providing here the solution. By the way if you get solution for the same please share it with others.


Related Discussions

Related Forums