Re: Need for ISERR and ISNA Makes formula too Long
- From: pzeitlin@xxxxxxxxx
- Date: Mon, 19 Nov 2007 13:14:03 -0800 (PST)
On Nov 19, 12:00 pm, pzeit...@xxxxxxxxx wrote:
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.
.
- References:
- Need for ISERR and ISNA Makes formula too Long
- From: pzeitlin
- Need for ISERR and ISNA Makes formula too Long
- Prev by Date: RE: IF STATEMENT, GREATER THEN 0 ADD ANOTHER CELLS AMOUNT
- Next by Date: Re: IF Statement with text
- Previous by thread: Re: Need for ISERR and ISNA Makes formula too Long
- Next by thread: RE: Redirecting a formula in an "IF THEN" statement
- Index(es):
Relevant Pages
|
