| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need for ISERR and ISNA Makes formula too Long
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+In puts!CQ35), 1)*(((VLOOKUP($F13,(INDIRECT(CONCATENATE($C13,"_ho urly_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)="P artner","Partner","Staff")),INDIRECT(CONCATENATE($ C13,"_fringe_rate")),MATCH(CW $6,Rates!$A$156:$I$156,1),0)) +VLOOKUP($F13,INDIRECT((CONCATENATE($C13,"_oh_rate s"))),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($B 13,'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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need for ISERR and ISNA Makes formula too Long
|
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need for ISERR and ISNA Makes formula too Long
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 On Nov 19, 12:00 pm, 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+In puts!CQ35), 1)*(((VLOOKUP($F13,(INDIRECT(CONCATENATE($C13,"_ho urly_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)="P artner","Partner","Staff"-)),INDIRECT(CONCATENATE($C13,"_fringe_rate")),MATC H(CW $6,Rates!$A$156:$I$156,1),0)) +VLOOKUP($F13,INDIRECT((CONCATENATE($C13,"_oh_rate s"))),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($B 13,'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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need for ISERR and ISNA Makes formula too Long
On Nov 19, 12:23 pm, JP wrote:
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 On Nov 19, 12:00 pm, 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+In puts!CQ35), 1)*(((VLOOKUP($F13,(INDIRECT(CONCATENATE($C13,"_ho urly_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)="P artner","Partner","Staff"--)),INDIRECT(CONCATENATE($C13,"_fringe_rate")),MATC H(CW $6,Rates!$A$156:$I$156,1),0)) +VLOOKUP($F13,INDIRECT((CONCATENATE($C13,"_oh_rate s"))),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($B 13,'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- Hide quoted text - - 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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need for ISERR and ISNA Makes formula too Long
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 On Nov 19, 12:37 pm, wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need for ISERR and ISNA Makes formula too Long
On Nov 19, 12:49 pm, JP wrote:
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 On Nov 19, 12:37 pm, wrote: 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.- Hide quoted text - - 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need for ISERR and ISNA Makes formula too Long
On Nov 19, 12:00 pm, 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+In puts!CQ35), 1)*(((VLOOKUP($F13,(INDIRECT(CONCATENATE($C13,"_ho urly_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)="P artner","Partner","Staff"-)),INDIRECT(CONCATENATE($C13,"_fringe_rate")),MATC H(CW $6,Rates!$A$156:$I$156,1),0)) +VLOOKUP($F13,INDIRECT((CONCATENATE($C13,"_oh_rate s"))),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($B 13,'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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use both ISNA and ISERROR in a long formula | Excel Worksheet Functions | |||
| What does this function do =+IF(ISERR(F27/G27),0,F27/G27) | Excel Worksheet Functions | |||
| ISERR | Excel Worksheet Functions | |||
| How do I use ISNA on this formula | Excel Worksheet Functions | |||
| IF(ISNA formula problem | Excel Worksheet Functions | |||
Linear Mode
