# Cell math formula

#### johnwill8258

##### New Member
=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

Last edited:

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Care to provide an input sample and a corresponding output without reference to any formula?

Rich (BB code):
``````=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:

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

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

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

Last edited:
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

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."

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:

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

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

Rich (BB code):
``````=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 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.

Replies
2
Views
388
Replies
1
Views
101
Replies
1
Views
151
Replies
4
Views
159
Replies
26
Views
852

### Forum statistics

1,203,542
Messages
6,056,017
Members
444,840
Latest member
RazzelDazel ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back