nested function

  • Thread starter Thread starter wiasta
  • Start date Start date
W

wiasta

hi,
can you let me know please, how deep (how many level) this function is
nested?
=IF(B51>F51;CONCATENATE(">";D51);IF(B51<F46;CONCATENATE("<";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
 
wiasta explained :
hi,
can you let me know please, how deep (how many level) this function is
nested?
=IF(B51>F51;CONCATENATE(">";D51);IF(B51<F46;CONCATENATE("<";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
Click to expand...

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(B51>F51,MakeGreaterThan,IF(B51<F46,MakeLessThan,BuildForecast))

HTH
 
wiasta explained :




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(B51>F51,MakeGreaterThan,IF(B51<F46,MakeLessThan,BuildForecast))

HTH
Click to expand...

thank you, but I still do not know how many this function is nested!
Is that more than double neting?
=IF(B51>F51;CONCATENATE(">";D51);IF(B51<F46;CONCATENATE("<";D46);
(10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2))))))
 
wiasta formulated on Wednesday :
thank you, but I still do not know how many this function is nested!
Is that more than double neting?
=IF(B51>F51;CONCATENATE(">";D51);IF(B51<F46;CONCATENATE("<";D46);
(10^(FORECAST(B51;OFFSET(E45;D52-1;;2);OFFSET(F45;D52-1;;2))))))
Click to expand...

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>
 
wiasta formulated on Wednesday :







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 -
Click to expand...

Garry, thanks a lot!
 
Back
Top