shorten formula, if possible

[brockk]

Hi to all,

is there a way to shorten this formula:

{=IF($A141="","",IF(OR($F141=0,$D141="",$D141>39814),INDEX(DBASE!$L$3:$FC$483,MATCH($A141,DBASE!$A$3:$A$483,0),MATCH($R$1,DBASE!$L$3:$FC$3,0)),VLOOKUP($A141,TEMPLATE!$B$7:$N$107,13,FALSE)))}

for some unknown reason I get #n/a error and don't know why because this is a copied formula and all other cells are reporting correct values except this one. Any suggestions? Thanks in advance.

 

[xld]

So is your goal to shorten, or correct it? If it is the latter, tell us about teh data it fails on.

 

[Domski]

Just a small point but your example seems to suggest that you are confirming the formula as an array formula with Ctrl+Shift+Enter, hence the {} around it, but I can't see that is necessary.

Dom

 

[brockk]

thanks fore the quick reply. Actually, I would like both shorten as well as correct. The reason being that the file is becoming quite big that it's obviuosly taking more time to calculate the thousands of formulas that compile the file.

As for the explanation, this file is compiled of a couple of w/s the basics being: TEMPLATE!, DBASE! and VOUCHER! amongst others.
The formula posted in the o.p. is located in the VOUCHER! w/s in which based on the actual data entered in the TEMPLATE! w/s will determine the actual assistance I will be claiming for as identified in my DBASE! w/s. Since, different situations can affect the actual result of assistance to request, the info. will rely on whatever data value is entered in the TEMPLATE! w/s in combination with the values that are looked up in the DBASE! w/s.

Up until now, I haven't had this experience of an #n/a error because this data was actually affected 5 times, each giving me a different result, in order to balance out my voucher. For an unknown cause, it calculated perfectly the first 4 affects but in this last one it gave me this error. The array values are correct. The match values are equivalent to the other cells (obviously, with the change of cell references as they are copied down) but with the exception that this error appears.

HTH!

 

[wsjackman]

have you tried evaluate formula to identify which section is giving the #na?

 

[brockk]

Yes, actually I have and it was giving me the #n/a with the $d141 cell, I checked the formula in this cell and it is also another formula copied down and there are no errors found. HTH.

 

[brockk]

Yes, actually I have and it was giving me the #n/a with the $d141 cell, I checked the formula in this cell and it is also another formula copied down and there are no errors found. HTH.

Update from this last post. After further trials I find that the n/a errors are appearing on all of the cases where in column D a result is not found. Is there a way that altering the original post formula can go around this situation. That is, if a n/a value is appearing in column D (example D141) that it can match the criterias accordingly? Thanks in advance.

 

[wsjackman]

I do not understand what you mean by
"where in column D a result is not found"

 

[brockk]

I do not understand what you mean by
"where in column D a result is not found"

I didn't realize that when the resulting value from the formula in columns D are not available because no result will affect the column D cells, a N/A shows up (only thing is that I forgot that I have it will conditional format that will convert the fonts to a white, leaving it transparent for those who see the w/s).

Since in my o.p. I had mentioned that ALL prior occurences had a value, I wasn't getting an error. But, when rearranging values a playing around with it I stumped on the #n/a error on all of the instances that the cells in column D were with a n/a value.

My problem now is, if there is a way to go around the n/a error so it can complete the "Index, etc. part of the original formula and if that is possible I think that will be the solution to my problem.

p.d. could the isna() function work and if so how would it be applicable within the original post formula. Thanks in advance for any help.

 

[brockk]

hints anyone?