Cell math formula

[johnwill8258]

=IF(OR(OR(OR(G15>=0.01, G15<=-0.01), K15>0.01), L15>=0.01,), IF(N14<>"", N14, N13)-G15+K15-L15-M15, "")

Above formula is applied to a table column. This equation will pretty much check several cells(G,K,L), then check N cell's(current N15) above cells if any are empty, if not allow skipping one cell, but no more, otherwise leave it blank.

I would like to allow skipping more than one cell. In order to do that, I will need to change up N13 part in IF(N14<>"", N14, N13), where N13 part will instead recursively call N14<>"", N14 portion, with decrementing N14. Is there a excel formula that enables that? Or if you have suggestion for another method, let me know.

Thank you

 

[Aladin Akyurek]

Care to provide an input sample and a corresponding output without reference to any formula?

 

[joeu2004]

=IF(OR(OR(OR(G15>=0.01, G15<=-0.01), K15>0.01), L15>=0.01,),
  IF(N14<>"", N14, N13)-G15+K15-L15-M15, "")

Simplify the nested-OR expression to:

OR(G15>=0.01, G15<=-0.01, K15>0.01, L15>=0.01)

I would like to allow skipping more than one cell. In order to do that, I will need to change up N13 part in IF(N14<>"", N14, N13), where N13 part will instead recursively call N14<>"", N14 portion, with decrementing N14.

Put another way: you want the IF(N14<>"", N14, N13) expression to return the last value in N1:N14 that is not the null-string, assuming there is one. Right?

Try: VLOOKUP(1E300,N1:N14).

1E300 is "some large value greater than any likely value".

If that does not work, tell us more about the values in N1:N14.

In summary, try the following:

=IF(OR(G15>=0.01, G15<=-0.01, K15>0.01, L15>=0.01),
  VLOOKUP(1E300,N1:N14)-G15+K15-L15-M15, "")

 

[johnwill8258]

Thank you but excel doesn't like the expression you gave me.

 

[johnwill8258]

Sorry for late reply. Here is the sample screenshot

As shown in N25 and N26, the formula allows skipping a line. But what I want is that formula allows skipping more than one.
*the cell in first line, N14, is simply =K14

 

[FDibbins]

Thank you but excel doesn't like the expression you gave me.

If you just used the OR statement suggested, then you need to putthat into the rest of your formula

 

[johnwill8258]

If you just used the OR statement suggested, then you need to putthat into the rest of your formula

Yes I did that. However I receive a message:

"You've entered too few arguments for this function.
To get help with this function, click OK to close this message. Then click the Insert Function button located to the left of the equal sign in your formula."

 

[joeu2004]

I receive a message: "You've entered too few arguments for this function. [...]"

My mistake: I typed VLOOKUP instead of LOOKUP. The intended formula is:

=IF(OR(G15>=0.01, G15<=-0.01, K15>0.01, L15>=0.01),
LOOKUP(1E300,N1:N14)-G15+K15-L15-M15, "")

 

[johnwill8258]

My mistake: I typed VLOOKUP instead of LOOKUP. The intended formula is:

=IF(OR(G15>=0.01, G15<=-0.01, K15>0.01, L15>=0.01),
LOOKUP(1E300,N1:N14)-G15+K15-L15-M15, "")

Thank you it works!
One thing though, since this is a table column, the starting value of the array is to be N14.
So, it'll be LOOKUP(1E300,N14:N14])-G15+K15-L15-M15, ""), where N14 needs to remain static, unchanging to the whole column since applying the formula to whole column will increment it.
After some search, I found out that it is called "locked reference".
=IF(OR(G15>=0.01, G15<=-0.01, K15>0.01, L15>=0.01), LOOKUP(1E+300,N$14:N14)-G15+K15-L15-M15, "")
Placing $ before a variable that I wish to lock will work.

Thank you for all the help

 

[joeu2004]

One thing though, since this is a table column, the starting value of the array is to be N14.
So, it'll be LOOKUP(1E300,N14:N14])-G15+K15-L15-M15, ""),
where N14 needs to remain static, unchanging to the whole column since applying the formula to whole column will increment it.
After some search, I found out that it is called "locked reference".
=IF(OR(G15>=0.01, G15<=-0.01, K15>0.01, L15>=0.01), LOOKUP(1E+300,N$14:N14)-G15+K15-L15-M15, "")

I am not sure if you still need help, or if "after some search", you have found the solution that you want.

My assumption was: you want the last value in some fixed column range. So you might write N$14:N$10000. Change 10000 if you might ever have more than that much data. Thus, the lookup range is unchanged as you copy the formula down a column.

However, your second formula above suggests that you want to find the last value in some dynamic column range, which increases as you copy the formula down a column. That is, N14:N14, N14:N15, N14:N16 etc.

If that is the case, you are correct to write N$14:N14 initially, which changes to N$14:N15, N$14:N16 etc as you copy the formula down a column.