Shorten a formula
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" wrote:
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))))))))))))
|