Thursday, August 8, 2013

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:
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,

1. Wow, that was awesome... Thanks for that one. I'll definitely come back at you, when I need help.

Thanks a lot, best,
T.

2. Sorry to bother you again, but i can't get it to work in my own table. I put it into the example table I attached and changed J10 to A4, A5 etc. and J11 to D4, D5 etc. so that Level and Difficulty is picked out of the existing tables. That worked fine.
Then I entered it into my own table and changed the ranges respectively but the result is a #VALUE! error:

error: Invalid query: Query parse error: Encountered " "," ", "" at line 1, column 22. Was expecting one of: "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and" ... "or" ...

Whats wrong? I checked It repeatedly on typos and mistakes but there aren't any

Thanks again