# Evaluating the results of a formula within an IF() statement, without duplicating formula?

1. ## Evaluating the results of a formula within an IF() statement, without duplicating formula?

A few weeks ago I posted a question about returning and displaying the minimum value between date ranges. There's a column of ascending dates, and next to that is a column indicating product availability. I wanted to return the minimum number available between when we had new product coming on. CAntosh provided me with an awesomely simple formula to return what I needed:

``Please Login or Register  to view this content.``
I'd like to add a complication to this that so that it only returns a number if it's greater than a value stored in a different cell. I'm able to do this somewhat messily if I use an IF statement:

``Please Login or Register  to view this content.``
So here's the primary question for this topic: Is it possible to do this without repeating the formula in the criteria and the result of the IF statement? I'm worried that it's going to add calculation overhead to a workbook that's already quite large.

I've attached an example file where this formula will be used. It's in range Y46:BI49

2. ## Re: Evaluating the results of a formula within an IF() statement, without duplicating form

So here's the primary question for this topic: Is it possible to do this without repeating the formula in the criteria and the result of the IF statement? I'm worried that it's going to add calculation overhead to a workbook that's already quite large.
I've looked at this off and on over the last few days. It took me a while to understand the dynamics of the "floating" range references.

Unless I am missing a simple step if it is possible to do what you describe I anticipate an array formula. If that is correct and you are not aware of it array formulas are resource hungry. I would be more concerned about the impact of that upon overhead than I would the repeating calculations. The repeating calculations in the upload appear to be rather mild respecting overhead. I could be wrong.

I couldn't help but notice the labor and time intensive nature of the "floating" range references. That is a lot of manual typing.

If you are interested I managed to work out a dynamic range construction to replace the manually entered ranges. Enter it once in Y46 ... fill down and across. Despite it's size (294 characters including the IF/MAX/MIN) it is constructed of INDEX/MATCH combinations. Those are quite fast.

Though I couldn't find a simple non-repeating solution, replacing the manually typed ranges with that dynamic range returns the same results.

3. ## Re: Evaluating the results of a formula within an IF() statement, without duplicating form

Originally Posted by FlameRetired
I've looked at this off and on over the last few days. It took me a while to understand the dynamics of the "floating" range references.

Unless I am missing a simple step if it is possible to do what you describe I anticipate an array formula. If that is correct and you are not aware of it array formulas are resource hungry. I would be more concerned about the impact of that upon overhead than I would the repeating calculations. The repeating calculations in the upload appear to be rather mild respecting overhead. I could be wrong.

I couldn't help but notice the labor and time intensive nature of the "floating" range references. That is a lot of manual typing.

If you are interested I managed to work out a dynamic range construction to replace the manually entered ranges. Enter it once in Y46 ... fill down and across. Despite it's size (294 characters including the IF/MAX/MIN) it is constructed of INDEX/MATCH combinations. Those are quite fast.

Though I couldn't find a simple non-repeating solution, replacing the manually typed ranges with that dynamic range returns the same results.
Dave, thanks for spending the mental time on this! It's a pretty specific problem that probably won't apply to many other people...

I've already got the floating formulas built. I assembled them in vertical format, and then used a combination of find and replace and transpose to align them horizontally. Thankfully all of the other worksheets I need to apply these to are laid out exactly the same, so it's just a matter of copying and pasting from here on out.

If you're willing to share I'd be interested in the alternative you came up with.

Thanks!

4. ## Re: Evaluating the results of a formula within an IF() statement, without duplicating form

Sure. See the attached.

The formula
Formula:
`Please Login or Register  to view this content.`

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1