Display value based on current month

[Cristan]

Hello

I am trying to display the value written in the current month, in another cell. So when its July, i want the value that is written in the table, under juli, to be displayed in another tabel below. Like this:

So that the data in July (97,00%) is displayed in the marked cell (Below "Status på mål").

What i have used for the YTD is this: =AVERAGE(OFFSET(E6;0;0;1;MATCH(C22;Tabel12[[#Headers];[Januar]:[December]];0))) ,but i dont want it to average, i only want the show the current value in the current month.
I thought i might tweak this in a way, but i cant really come up with a solution..

I hope it makes sense and that someone can help me with this!

Thanks!

 

[jorismoerings]

Hi,

Your screenshot is lacking Column and row header so i'm working on the assumption cell references in your formula are correct!

The match part of your current formula :

=MATCH(C22;Tabel12[[#Headers];[Januar]:[December]];0)

is providing the number of the month in the array. In This case 7.
So your current formula evaluates into:

1. Start at Cell E6;
2. 0 means "go 0 rows down"
3. 0 means "go 0 rows right"
4. 1 means "take 1 row"
5. the match part evaluates into 7 and means "include 7 columns"

so the formula evaluates into

=AVERAGE(E6:K6)

To get to a year to date formula, just change AVERAGE into SUM and that's done.

To only get the monthly value:

1. start at Cell E6
2. you're on the correct line so go 0 rows down
3. you're in the january month so july is 6 columns to the right, the match mart in the formula evaluates to 7 so enter 7-1

This creates the next formula which will only show the monthly result:

=OFFSET(E6;0;MATCH(C22;Tabel12[[#Headers];[Januar]:[December]];0)-1))

Hope this helps.

 

[Cristan]

This worked, that you for your help!