Google Spreadsheet Game Level Calculations

Question:

( by Timon Henze )

Hey guys,

the following problem, think of it as a Experience Points and Level system for games:

I have 10 difficulties, which yield certain amounts of experience points, e.g.: Difficulty 1 yields 20XP, Difficulty 2 yields 45XP, Difficulty 3 yields 100XP etc.
I have a data set with wich I play around to set Level Caps e.g.: At 1000XP I'm at Level 2, at 3300XP I'm at Level 3, with 6500XP I'm at level 4 etc.

Now I want to have a function, wich outputs the result of accomplishing a task of a certain difficulty at a certain Level, e.g.:

I'm at Level 3 and I want to know which level result when picking a difficulty 5 task
I'm at Level 10 and I want to know which level result when picking a difficulty 3 task

and so on. So the result should be a level number which is picked from the Level Caps table.

I attached a simple example file with the wanted calculations shown on the right.

Thanks for coping!

Solution:


Have a look at the following screenshot of my Spreadsheet:

As you can see in the above screenshot, values in Cell J10 and J11 has to be inputted manually and I have the following formula in Cell J12:

=query(A4:B22;"select A where B<="&(vlookup(J10;A4:B22;2;false)+vlookup(J11;D4:E13;2;false))&"order by B desc limit 1")

the above formula will automatically calculate it as per your requirement and give you the result.

So, now if you put the value in (Your current Level) Cell J10 as: 3

and the value in (Picked up Difficulty Task) Cell J11 as: 5
You will get the result in Cell J12 as: 5
that is you will reach to Level: 5

And if you put the value in (Your current Level) Cell J10 as: 10
and the value in (Picked up Difficulty Task) Cell J11 as: 3
You will get the result in Cell J12 as: 10

that is you will reach to Level: 10

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive