I am playing a game, and I want to get the average value of the items available to you. As you level up, new items are available, and you still have access to items of the previous level.
I tried working with ChatGPT. It gave me a formula:
=QUERY(B:A, "SELECT A, AVG(B) WHERE A IS NOT NULL GROUP BY A")
Here is the table:
| Value | Level |
|---|---|
| 1 | 1 |
| 3 | 3 |
| 5 | 4 |
| 7 | 7 |
| 9 | 9 |
| 37 | 9 |
| 61 | 10 |
| 7 | 1 |
| 10 | 5 |
| 15 | 10 |
| 5 | 2 |
| 44 | 5 |
| 55 | 8 |
| 12 | 4 |
| 25 | 6 |
| 39 | 11 |
| 45 | 12 |
| 76 | 14 |
| 14 | 7 |
| 40 | 5 |
| 40 | 5 |
| 40 | 5 |
| 100 | 5 |
| 100 | 5 |
| 100 | 5 |
In this case, A and B in the aforementioned formula would be reversed.
I can't get it to include values from the previous levels. It gives me this:
| Level | avg Value |
|---|---|
| 1 | 4 |
| 2 | 5 |
| 3 | 3 |
| 4 | 8.5 |
| 5 | 59.25 |
| 6 | 25 |
| 7 | 10.5 |
| 8 | 55 |
| 9 | 23 |
| 10 | 38 |
| 11 | 39 |
| 12 | 45 |
| 14 | 76 |
It is grouping by the level, which is a step in the right direction.. but it needs to also include previous levels. Without manually adding these values to each subsequent level, how do I do this?
I found the best solution.
=AVERAGEIFS(C:C, B:B, "<="&E2)
It gets values of levels equal and lesser to the current level.