+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    02-11-2014
    Location
    North Carolina
    MS-Off Ver
    Excel 2016
    Posts
    19

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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.
    Dave

  3. #3
    Registered User
    Join Date
    02-11-2014
    Location
    North Carolina
    MS-Off Ver
    Excel 2016
    Posts
    19

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

    Quote Originally Posted by FlameRetired View Post
    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. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

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

    Sure. See the attached.

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] IF statement formula continues to effect previous results
    By nelson2101 in forum Excel General
    Replies: 5
    Last Post: 05-23-2017, 03:25 AM
  2. [SOLVED] How to prevent duplicating a long formula as part of a formula
    By JimDandy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-23-2016, 12:17 AM
  3. Replies: 3
    Last Post: 01-22-2015, 12:59 PM
  4. my if formula not evaluating correctly
    By zazzz in forum Excel General
    Replies: 7
    Last Post: 10-29-2012, 02:27 PM
  5. Evaluating Array Formula in VBA and sending Results Only to Spreadsheet
    By NBVC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-13-2009, 09:20 AM
  6. Formula not evaluating immediately
    By Barb Reinhardt in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-18-2006, 02:55 PM
  7. Evaluating results of a concatenate formula, as a formula
    By dodger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-09-2005, 09:05 AM

Bookmarks

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