# Dynamic formulas IF/LOOKUP?

1. ## Dynamic formulas IF/LOOKUP?

Hi!

I will have excel to calculate the percent change between to values where an improvement is always shown as a positive value and a decline is always shown as a negative value. My problem is that I want excel to calculate this between the to latest values only i.e. if column J is blank, calculate with column I and H etc. also if a player misses a test and column J is blank but so is column H, then it would be really nice if excel could use the data from I and G.

I have absolutely no idea how to write a dynamic formula like this. I am familiar with IF formulas but I couldn't find a way for that to work :/

Anyone able to help me? I attached a simple workbook with some numbers to calculate on.

Thanks!

Markus

2. ## Re: Dynamic formulas IF/LOOKUP?

can you tell me the expected result manually

3. ## Re: Dynamic formulas IF/LOOKUP?

hi anghicole. try this array formula:
=LOOKUP(2,1/(D2:N2<>""),D2:N2)-INDEX(D2:N2,LARGE(IF(D2:N2<>"",COLUMN(D2:N2)),2)-COLUMN(D2)+1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

and change all my commas to semicolons if your separators are the latter

4. ## Re: Dynamic formulas IF/LOOKUP?

benishiryo that seems to work perfectly well thank you so much!!

5. ## Re: Dynamic formulas IF/LOOKUP?

Hi,

I would've thought the percentage increase/decrease should be measured viz:

(Latest Score-Previous Score)/Previous Score

Non-array alternative to do this (in O2 and copy down):

=SUMPRODUCT(N(OFFSET(D2,,LARGE(INDEX((D2:N2<>"")*(COLUMN(D2:N2)-MIN(COLUMN(D2:N2))),,),{1,2}),,))*{1,-1})/OFFSET(D2,,LARGE(INDEX((D2:N2<>"")*(COLUMN(D2:N2)-MIN(COLUMN(D2:N2))),,),2),,)

Regards

6. ## Re: Dynamic formulas IF/LOOKUP?

XOR LX, you're totally right! I did get some strange percentages on some of the results. Your formula works perfect, and with a correct calculation of the percentages! Thank you so much for the quick correction!!

7. ## Re: Dynamic formulas IF/LOOKUP?

You're welcome!

There are currently 1 users browsing this thread. (0 members and 1 guests)