Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Shorten a formula
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8
Default Shorten a formula
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))))))))))))


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,441
Default Shorten a formula
Use words to describe your data table(s), and what you want to do with the formula.

HTH,
Bernie
MS Excel MVP


"Jeze77" wrote in message
...
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))))))))))))




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8
Default Shorten a formula
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" wrote:

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

HTH,
Bernie
MS Excel MVP


"Jeze77" wrote in message
...
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))))))))))))





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,441
Default Shorten a formula
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" wrote in message
...
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" wrote:

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

HTH,
Bernie
MS Excel MVP


"Jeze77" wrote in message
...
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))))))))))))







Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8
Default Shorten a formula
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" wrote:

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" wrote in message
...
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" wrote:

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

HTH,
Bernie
MS Excel MVP


"Jeze77" wrote in message
...
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))))))))))))








Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,441
Default Shorten a formula
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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

HTH,
Bernie
MS Excel MVP


"Jeze77" wrote in message
...
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))))))))))))










Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Shorten Formula Lemmesee Excel Worksheet Functions 3 January 7th 07 10:30 PM
Shorten a Formula Kevin Excel Worksheet Functions 4 January 30th 06 02:31 AM
Shorten an IF formula Joker Excel Discussion (Misc queries) 4 December 10th 05 12:30 PM
shorten formula PACable Excel Worksheet Functions 8 October 27th 05 12:39 AM
Trying to shorten (or use new) formula Monk Excel Discussion (Misc queries) 1 June 29th 05 06:23 PM


All times are GMT +1. The time now is 06:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement