shorten formula, if possible

brockk

Board Regular
Joined
Jul 1, 2006
Messages
146
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
So is your goal to shorten, or correct it? If it is the latter, tell us about teh data it fails on.
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Board Regular
Joined
Jul 1, 2006
Messages
146
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

Well-known Member
Joined
Jun 19, 2008
Messages
2,465

ADVERTISEMENT

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

brockk

Board Regular
Joined
Jul 1, 2006
Messages
146
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

Board Regular
Joined
Jul 1, 2006
Messages
146

ADVERTISEMENT

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

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
I do not understand what you mean by
"where in column D a result is not found"
 

brockk

Board Regular
Joined
Jul 1, 2006
Messages
146
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,687
Messages
5,597,541
Members
414,154
Latest member
thevaper

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top