Shorten a formula

G

Guest

How can i shorten this?

IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.75,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)))
(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))))))))))))
 
B

Bernie Deitrick

Use words to describe your data table(s), and what you want to do with the formula.

HTH,
Bernie
MS Excel MVP
 
G

Guest

Basically i want it to look up the value closest to 0 (in eighths) and return
the corresponding rate, since i'm dealing with negatives, this is the best
that i could come up with. The order is 0, -.125, +.125, -.25, +.25, -.375,
+.375, -.5, +.5, -.625, +.625, -.75 and so on.
My data table shows the rates i want returned in column A and its looking up
the values in column N.
I can only go as high as -.25, anything further I receive an error message
stating that the formula is too long.

Bernie Deitrick said:
Use words to describe your data table(s), and what you want to do with the formula.

HTH,
Bernie
MS Excel MVP


Jeze77 said:
How can i shorten this?

IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.75,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))))))))))))
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Jeze,

Use array formula (Enter using Ctrl-Shift-Enter)

=INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,
IF(ISERROR(MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0)),MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0),MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0)))

If you enter it correctly, Excel will curround it with curly braces {.....}s

Note that this formula will preferentially return the positive value if there is a tie between a
negative and positive for magnitude.

HTH,
Bernie
MS Excel MVP


Jeze77 said:
Basically i want it to look up the value closest to 0 (in eighths) and return
the corresponding rate, since i'm dealing with negatives, this is the best
that i could come up with. The order is 0, -.125, +.125, -.25, +.25, -.375,
+.375, -.5, +.5, -.625, +.625, -.75 and so on.
My data table shows the rates i want returned in column A and its looking up
the values in column N.
I can only go as high as -.25, anything further I receive an error message
stating that the formula is too long.

Bernie Deitrick said:
Use words to describe your data table(s), and what you want to do with the formula.

HTH,
Bernie
MS Excel MVP


Jeze77 said:
How can i shorten this?

IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.75,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Bernie.
Is there not a way for the negative to return?
That's what i keep getting stuck on and the reason i created such a long
formula, I need the negative to return first if available.

Bernie Deitrick said:
Jeze,

Use array formula (Enter using Ctrl-Shift-Enter)

=INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,
IF(ISERROR(MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0)),MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0),MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0)))

If you enter it correctly, Excel will curround it with curly braces {.....}s

Note that this formula will preferentially return the positive value if there is a tie between a
negative and positive for magnitude.

HTH,
Bernie
MS Excel MVP


Jeze77 said:
Basically i want it to look up the value closest to 0 (in eighths) and return
the corresponding rate, since i'm dealing with negatives, this is the best
that i could come up with. The order is 0, -.125, +.125, -.25, +.25, -.375,
+.375, -.5, +.5, -.625, +.625, -.75 and so on.
My data table shows the rates i want returned in column A and its looking up
the values in column N.
I can only go as high as -.25, anything further I receive an error message
stating that the formula is too long.

Bernie Deitrick said:
Use words to describe your data table(s), and what you want to do with the formula.

HTH,
Bernie
MS Excel MVP


How can i shorten this?

IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.75,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Sure, just need to swap a few negative signs. This should work (again enter using Ctrl-Shift-Enter)

=INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,
IF(ISERROR(MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0)),MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0),MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0)))


HTH,
Bernie
MS Excel MVP


Jeze77 said:
Thanks Bernie.
Is there not a way for the negative to return?
That's what i keep getting stuck on and the reason i created such a long
formula, I need the negative to return first if available.

Bernie Deitrick said:
Jeze,

Use array formula (Enter using Ctrl-Shift-Enter)

=INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,
IF(ISERROR(MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0)),MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0),MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0)))

If you enter it correctly, Excel will curround it with curly braces {.....}s

Note that this formula will preferentially return the positive value if there is a tie between a
negative and positive for magnitude.

HTH,
Bernie
MS Excel MVP


Jeze77 said:
Basically i want it to look up the value closest to 0 (in eighths) and return
the corresponding rate, since i'm dealing with negatives, this is the best
that i could come up with. The order is 0, -.125, +.125, -.25, +.25, -.375,
+.375, -.5, +.5, -.625, +.625, -.75 and so on.
My data table shows the rates i want returned in column A and its looking up
the values in column N.
I can only go as high as -.25, anything further I receive an error message
stating that the formula is too long.

:

Use words to describe your data table(s), and what you want to do with the formula.

HTH,
Bernie
MS Excel MVP


How can i shorten this?

IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.75,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok, it sort of works...some days there may be values in column N that are
blank, the formula won't calculate unless all the cells in the range contains
a #. The file with the range comes from an outside source, to manually
update blank fields is too time consuming. Any suggestions?

Thank you for all of your help,
Jessica

Bernie Deitrick said:
Sure, just need to swap a few negative signs. This should work (again enter using Ctrl-Shift-Enter)

=INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,
IF(ISERROR(MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0)),MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0),MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0)))


HTH,
Bernie
MS Excel MVP


Jeze77 said:
Thanks Bernie.
Is there not a way for the negative to return?
That's what i keep getting stuck on and the reason i created such a long
formula, I need the negative to return first if available.

Bernie Deitrick said:
Jeze,

Use array formula (Enter using Ctrl-Shift-Enter)

=INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,
IF(ISERROR(MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0)),MATCH(-MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25,
0),MATCH(MIN(ABS([NEAST2_1.XLW]Sheet1!$N$16:$N$25)), [NEAST2_1.XLW]Sheet1!$N$16:$N$25, 0)))

If you enter it correctly, Excel will curround it with curly braces {.....}s

Note that this formula will preferentially return the positive value if there is a tie between a
negative and positive for magnitude.

HTH,
Bernie
MS Excel MVP


Basically i want it to look up the value closest to 0 (in eighths) and return
the corresponding rate, since i'm dealing with negatives, this is the best
that i could come up with. The order is 0, -.125, +.125, -.25, +.25, -.375,
+.375, -.5, +.5, -.625, +.625, -.75 and so on.
My data table shows the rates i want returned in column A and its looking up
the values in column N.
I can only go as high as -.25, anything further I receive an error message
stating that the formula is too long.

:

Use words to describe your data table(s), and what you want to do with the formula.

HTH,
Bernie
MS Excel MVP


How can i shorten this?

IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
IF(ISERROR(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.75,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.625,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.5,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.375,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.25,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0)),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0+0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0-0.125,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))),
(INDEX([NEAST2_1.XLW]Sheet1!$A$16:$A$25,MATCH(0,[NEAST2_1.XLW]Sheet1!$N$16:$N$25,0))))))))))))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MATCH to return row number not working in HLOOKUP 2
Formula returns #N/A 1
Excel Import Comments 3
error message handling #N/A 2
hiding an error value, using INDEX/MATCH 3
How to bring up a blank cell 2
how do i solve 'file not found' error when linking notebooks? 1
Auto Generate Sorting table(empty cell)?? 1

Top