3 Replies Latest reply on Jun 21, 2016 7:38 AM by Mark Fraser

# Using a middle value in a table calc(vs. first or last)

Desired Outcome:

 -200 -100 Base 100 200 300 400 500 Net Interest Income 9,945,683 9,795,236 10,110,267 10,244,034 10,398,101 10,489,930 10,512,070 10,514,746 Current Qtr % Change -1.63% -3.12% 1.32% 2.85% 3.76% 3.97% 4.00%

 Net   Interest Income 9,945,683 9,795,236 10,110,267 10,244,034 10,398,101 10,489,930 10,512,070 10,514,746 % Change -1.63% -3.12% 1.32% 2.85% 3.76% 3.97% 4.00%

I can get the desired results using a table calc if I put 'Base' first or last in the list.  However, I want 'Base' to be in the middle on my viz.

Looking for how to make a percentage of all values relative to 'Base'.(with Base in the middle of the table)

Examples in wkbk

Thanks for any suggestions!

• ###### 1. Re: Using a middle value in a table calc(vs. first or last)

Hi Mike

49 views, and nobody helps?!

OK, I'll try

Assuming so, use the following -

ZN(SUM([Values])) / LOOKUP(ZN(SUM([Values])), FIRST()+2)-1

The Key is the +2, to have Base in the middle, its in the third column.

We just adjust your first formula to move forward tot he third column (iin place of 1)

We could alternatively amend the last version instead

ZN(SUM([Values])) / LOOKUP(ZN(SUM([Values])), LAST()-5)-1

Output

Both should be exactly the same

Hope that helps

Cheers

Mark

• ###### 2. Re: Using a middle value in a table calc(vs. first or last)

Yes, Mark that is the expected output.  Thank you so much!!!  Silly me, I kept trying to adjust the position without the parenthesis.