|
nested function
hi,
can you let me know please, how deep (how many level) this function is nested? =IF(B51F51;CONCATENATE("";D51);IF(B51<F46;CONCAT ENATE("<";D46); (10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2)))))) The nesting shouldn't be more than double, otherwise I have to validate my excel spreadsheet at work before using it. thanks Sar |
nested function
wiasta explained :
hi, can you let me know please, how deep (how many level) this function is nested? =IF(B51F51;CONCATENATE("";D51);IF(B51<F46;CONCAT ENATE("<";D46); (10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2)))))) The nesting shouldn't be more than double, otherwise I have to validate my excel spreadsheet at work before using it. thanks Sar First thing I see here is that you are using ";" to separate your args, which doesn't work. Use a comma. You can safely 'nest' up to 7 IF() functions without any problems (in most cases). If you need more than that then you'll have to put Defined names to use so it handles a greater number of IFs for you. For example, you could put your last FALSE condition in a named formula: In the namebox: 'Sheet Name'!BuildForecast In the RefersTo box: =10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2))) You could also do similar with your CONCATENATE() conditions: In the namebox: 'Sheet Name'!MakeGreaterThan In the RefersTo box: =CONCATENATE("", D51) </ In the namebox: 'Sheet Name'!MakeLessThan In the RefersTo box: =CONCATENATE("<", D46) The resulting formula: =IF(B51F51,MakeGreaterThan,IF(B51<F46,MakeLessTha n,BuildForecast)) HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
nested function
On 16 Jun., 21:51, GS wrote:
wiasta explained : hi, can you let me know please, how deep (how many level) this function is nested? =IF(B51F51;CONCATENATE("";D51);IF(B51<F46;CONCAT ENATE("<";D46); (10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2)))))) The nesting shouldn't be more than double, otherwise I have to validate my excel spreadsheet at work before using it. thanks Sar First thing I see here is that you are using ";" to separate your args, which doesn't work. Use a comma. You can safely 'nest' up to 7 IF() functions without any problems (in most cases). If you need more than that then you'll have to put Defined names to use so it handles a greater number of IFs for you. For example, you could put your last FALSE condition in a named formula: * In the namebox: * * 'Sheet Name'!BuildForecast * In the RefersTo box: * * =10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2))) You could also do similar with your CONCATENATE() conditions: * In the namebox: * * 'Sheet Name'!MakeGreaterThan * In the RefersTo box: * * =CONCATENATE("", D51) </ * In the namebox: * * 'Sheet Name'!MakeLessThan * In the RefersTo box: * * =CONCATENATE("<", D46) The resulting formula: * =IF(B51F51,MakeGreaterThan,IF(B51<F46,MakeLessTha n,BuildForecast)) HTH thank you, but I still do not know how many this function is nested! Is that more than double neting? =IF(B51F51;CONCATENATE("";D51);IF(B51<F46;CONCAT ENATE("<";D46); (10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2)))))) |
nested function
wiasta formulated on Wednesday :
On 16 Jun., 21:51, GS wrote: wiasta explained : hi, can you let me know please, how deep (how many level) this function is nested? =IF(B51F51;CONCATENATE("";D51);IF(B51<F46;CONCAT ENATE("<";D46); (10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2)))))) The nesting shouldn't be more than double, otherwise I have to validate my excel spreadsheet at work before using it. thanks Sar First thing I see here is that you are using ";" to separate your args, which doesn't work. Use a comma. You can safely 'nest' up to 7 IF() functions without any problems (in most cases). If you need more than that then you'll have to put Defined names to use so it handles a greater number of IFs for you. For example, you could put your last FALSE condition in a named formula: * In the namebox: * * 'Sheet Name'!BuildForecast * In the RefersTo box: * * =10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2))) You could also do similar with your CONCATENATE() conditions: * In the namebox: * * 'Sheet Name'!MakeGreaterThan * In the RefersTo box: * * =CONCATENATE("", D51) </ * In the namebox: * * 'Sheet Name'!MakeLessThan * In the RefersTo box: * * =CONCATENATE("<", D46) The resulting formula: * =IF(B51F51,MakeGreaterThan,IF(B51<F46,MakeLessTha n,BuildForecast)) HTH thank you, but I still do not know how many this function is nested! Is that more than double neting? =IF(B51F51;CONCATENATE("";D51);IF(B51<F46;CONCAT ENATE("<";D46); (10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2)))))) It has 1 nested IF() inside the initial IF(). Not sure how you interpret "double nested" but I'd say it's single nested in that there's only one IF() "nested". Basically, count the IFs to determine the level of nesting. Usually, a single IF is not considered a "nested" construct and so a "double nested" IF() construct would have 2 IFs nested inside a single IF().<IMO -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
nested function
On 16 Jun., 23:42, GS wrote:
wiasta formulated on Wednesday : On 16 Jun., 21:51, GS wrote: wiasta explained : hi, can you let me know please, how deep (how many level) this function is nested? =IF(B51F51;CONCATENATE("";D51);IF(B51<F46;CONCAT ENATE("<";D46); (10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2)))))) The nesting shouldn't be more than double, otherwise I have to validate my excel spreadsheet at work before using it. thanks Sar First thing I see here is that you are using ";" to separate your args, which doesn't work. Use a comma. You can safely 'nest' up to 7 IF() functions without any problems (in most cases). If you need more than that then you'll have to put Defined names to use so it handles a greater number of IFs for you. For example, you could put your last FALSE condition in a named formula: In the namebox: 'Sheet Name'!BuildForecast In the RefersTo box: =10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2))) You could also do similar with your CONCATENATE() conditions: In the namebox: 'Sheet Name'!MakeGreaterThan In the RefersTo box: =CONCATENATE("", D51) </ In the namebox: 'Sheet Name'!MakeLessThan In the RefersTo box: =CONCATENATE("<", D46) The resulting formula: =IF(B51F51,MakeGreaterThan,IF(B51<F46,MakeLessTha n,BuildForecast)) HTH thank you, but I still do not know how many this function is nested! Is that more than double neting? =IF(B51F51;CONCATENATE("";D51);IF(B51<F46;CONCAT ENATE("<";D46); (10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2)))))) It has 1 nested IF() inside the initial IF(). Not sure how you interpret "double nested" but I'd say it's single nested in that there's only one IF() "nested". Basically, count the IFs to determine the level of nesting. Usually, a single IF is not considered a "nested" construct and so a "double nested" IF() construct would have 2 IFs nested inside a single IF().<IMO -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Zitierten Text ausblenden - - Zitierten Text anzeigen - Garry, thanks a lot! |
| All times are GMT +1. The time now is 05:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com