Need for ISERR and ISNA Makes formula too Long

[pzeitlin]

I have an extremely complex formula which works flawlessly (if ya'll
know how to reduce the size, let me know). The problem is the formula
references a lot of dynamic date ranges and some lookups. The result
of some of those calculations are proper N/A and #Ref errors. The
only way I am familar with dealing with those so they don't create
problems in my totals is to use iserr or isna with an if/then
statement that repeats the formula (once to determine an error, once
to say if it's ok...to calculate).

Since my formula is already near max length, any attempt to do that
would be too long. I know one solution is to reference another cell
with the formula, but my spreadsheet is already huge (208 columns and
53 lines) and doing that will add several megs. Is there a simpler
solution.

Here is the formula (since it's always requested):

=IF(contract_type=lists!$H$35,(Cost!CW67*(1-discount_rate)),
(IF(contract_type=lists!$H$34,
((IF(contract_fee_type=percentage_per_dollar,(1+Inputs!CQ35),
1)*(((VLOOKUP($F13,(INDIRECT(CONCATENATE($C13,"_hourly_rates"))),
(MATCH($G13,Rates!$A$119:$O$119,0)),
0)*(1+average_salary_increase)^((VLOOKUP((MID(CW
$5,6,4)),fiscal_year_code,2,0))+IF((VALUE((MID(CW
$5,3,2))))>=4,1,0))*(1+VLOOKUP((IF(LEFT($G13,7)="Partner","Partner","Staff")),INDIRECT(CONCATENATE($C13,"_fringe_rate")),MATCH(CW
$6,Rates!$A$156:$I$156,1),0))
+VLOOKUP($F13,INDIRECT((CONCATENATE($C13,"_oh_rates"))),MATCH(CW
$6,Rates!$A$156:$I$156,1),FALSE)))*(1+
(VLOOKUP($C13,ganda_rates,MATCH(CW$6,Rates!$A$156:$I$156,1),FALSE))))
+IF(contract_fee_type=dollars_per_hour,Inputs!CQ36,0))*Hours!CU14),
(IF(contract_type=lists!$H$36,(VLOOKUP((VLOOKUP($B13,'Rate Info'!$A
$8:$S$56,(MATCH(CW$6,'Rate Info'!$J$7:$S$7,1)+(COLUMNS('Rate Info'!$A
$7:$J$7)-1)),0)),tandm_rates,MATCH(Cost!CW$6,'T&M Rates'!$A$3:$K$3,1),
0)*Hours!CU14),0)))))

(if I am not the most efficient with the formulas..I apologize to the
experts). I suppose I could cut some down with a named range (maybe
even the solution to my problem)..just not smart enough how to make a
formula range dynamic like I need it.

Thanks for your help (and yeah, one day I will actually learn VB).

Paul

 

[Don Guillett]

One thing you can do is name your lookup ranges
Rates'!$A$3:$K$3
name
ra3 or ???
Rates!$A$119:$O$119
ra119

 

[JP]

1. You can replace CONCATENATE and the associated parentheses with the
"&" sign
2. You could place the formula in an out-of-the-way place (say, column
IV), allow the error to occur, then in your chosen column put
"=IF(ISNA(IV1),"",IV1)"


HTH,
JP

 

[pzeitlin]

1. You can replace CONCATENATE and the associated parentheses with the
"&" sign
2. You could place the formula in an out-of-the-way place (say, column
IV), allow the error to occur, then in your chosen column put
"=IF(ISNA(IV1),"",IV1)"

HTH,
JP









- Show quoted text -

Thanks for the advice so far. Obviously naming those ranges that I
haven't done so yet will help. Also, I appreciate the discussion on
removing concatenate. If there is no 'short-cut' to using the isserr/
isna with the if statement, then I will go back and make those
changes. I am still hoping that someone has a nifty idea to not have
to duplicate the formula when there isn't an error. The one thing I
can't do is hide the formula in a different place as every single
formula (11K of them) is different and that would require 11K new
formulas and will add significant size.

Thanks guys!

 

[JP]

ps- you have valid "#REF" errors?

The #REF! error value occurs when a cell reference is not valid.

If you fix that it might solve some of your problems.

--JP

 

[pzeitlin]

ps- you have valid "#REF" errors?

The #REF! error value occurs when a cell reference is not valid.

If you fix that it might solve some of your problems.

--JP



- Show quoted text -

It is valid, depending on the source of data. I am creating an
automated model to be used by others to forecast project costs. The
model allows dynamic date changes - meaning a project can be two
months or five years - the model is comprehensive enough to cover
both. If particular cell is trying to calculate a solution for a date
outside the period of performance, it's not going to find the relevant
information and thus return an error. Of course, you are right and
that's my whole issue I am trying to solve. The end user should not
see a error because of parameters beinge exclusionary to the project.
They should see zero or blanks. If I was better with visual basic, I
probably would have a way to fully deal with these issues.
Unfortunately, I don't.

Since I am stuck writing serial type formulas (particularly in my 3D
environment), you are going to run into those errors. I've taken up
the ideas of naming additional ranges, removing concatenate, etc. I
have even renamed ranges and sheets to cut down on file size, but the
formula is still extremely long and I will not be able to do the iserr/
isna solution in the same cell.

Thanks..I am very appreciative of the activiness and helpfulness of
this board!

v/r

Paul

 

[pzeitlin]

I have an extremely complex formula which works flawlessly (if ya'll
know how to reduce the size, let me know). The problem is the formula
references a lot of dynamic date ranges and some lookups. The result
of some of those calculations are proper N/A and #Ref errors. The
only way I am familar with dealing with those so they don't create
problems in my totals is to use iserr or isna with an if/then
statement that repeats the formula (once to determine an error, once
to say if it's ok...to calculate).

Since my formula is already near max length, any attempt to do that
would be too long. I know one solution is to reference another cell
with the formula, but my spreadsheet is already huge (208 columns and
53 lines) and doing that will add several megs. Is there a simpler
solution.

Here is the formula (since it's always requested):

=IF(contract_type=lists!$H$35,(Cost!CW67*(1-discount_rate)),
(IF(contract_type=lists!$H$34,
((IF(contract_fee_type=percentage_per_dollar,(1+Inputs!CQ35),
1)*(((VLOOKUP($F13,(INDIRECT(CONCATENATE($C13,"_hourly_rates"))),
(MATCH($G13,Rates!$A$119:$O$119,0)),
0)*(1+average_salary_increase)^((VLOOKUP((MID(CW
$5,6,4)),fiscal_year_code,2,0))+IF((VALUE((MID(CW
$5,3,2))))>=4,1,0))*(1+VLOOKUP((IF(LEFT($G13,7)="Partner","Partner","Staff"-)),INDIRECT(CONCATENATE($C13,"_fringe_rate")),MATCH(CW
$6,Rates!$A$156:$I$156,1),0))
+VLOOKUP($F13,INDIRECT((CONCATENATE($C13,"_oh_rates"))),MATCH(CW
$6,Rates!$A$156:$I$156,1),FALSE)))*(1+
(VLOOKUP($C13,ganda_rates,MATCH(CW$6,Rates!$A$156:$I$156,1),FALSE))))
+IF(contract_fee_type=dollars_per_hour,Inputs!CQ36,0))*Hours!CU14),
(IF(contract_type=lists!$H$36,(VLOOKUP((VLOOKUP($B13,'Rate Info'!$A
$8:$S$56,(MATCH(CW$6,'Rate Info'!$J$7:$S$7,1)+(COLUMNS('Rate Info'!$A
$7:$J$7)-1)),0)),tandm_rates,MATCH(Cost!CW$6,'T&M Rates'!$A$3:$K$3,1),
0)*Hours!CU14),0)))))

(if I am not the most efficient with the formulas..I apologize to the
experts). I suppose I could cut some down with a named range (maybe
even the solution to my problem)..just not smart enough how to make a
formula range dynamic like I need it.

Thanks for your help (and yeah, one day I will actually learn VB).

Paul

This is what I was looking for: setv/getv in the morefunc addin. This
basically sets a temporary variable to be called later in the formula.
However, this doesn't works totally well since I will be sending the
final model to others. Fortunately, I found this VB module online
that does that same thing:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Thanks Mr. Dunn. Basically your formula would be =if(V(condition
valuating)>2,V(),"No"). Where V the 1st time sets the paramater and V
the 2nd time calls back the parameter. Just thought I'd close out for
those curious.