+ Reply to Thread
Results 1 to 4 of 4

To set limit to value generated in a range

  1. #1
    Registered User
    Join Date
    02-15-2017
    Location
    Cochin
    MS-Off Ver
    windows 8
    Posts
    3

    To set limit to value generated in a range

    I have a set of multiple formulas that generate certain values. I need to set limit to the values generated. For instance, if the value is less than 200, I need the actual value which is less than 200, and if the value is greater than or equal to 200, I need 200 only. the formula is copied for reference. Someone plz help

    =IF(AND(O3="W",E3>='Workings Model'!$O$2,Trip!E3<'Workings Model'!$P$2),Trip!F3*'Workings Model'!$Q$2%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$3,Trip!E3<'Workings Model'!$P$3),Trip!F3*'Workings Model'!$Q$3%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$4,Trip!E3<'Workings Model'!$P$4),Trip!F3*'Workings Model'!$Q$4%,"")&IF(AND(Trip!O3="W",Trip!E3>='Workings Model'!$O$5,Trip!E3<'Workings Model'!$P$5),Trip!F3*'Workings Model'!$Q$5%,"")&IF(AND(O3="W",Trip!E3>='Workings Model'!$O$6,Trip!E3<'Workings Model'!$P$6),Trip!F3*'Workings Model'!$Q$6%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$7,Trip!E3<'Workings Model'!$P$7),Trip!F3*'Workings Model'!$Q$7%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$8,Trip!E3<'Workings Model'!$P$8),Trip!F3*'Workings Model'!$Q$8%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$9,Trip!E3<'Workings Model'!P10),Trip!F3*'Workings Model'!$Q$9%,"")&IF(AND(O3="E",E3>='Workings Model'!$O$10,Trip!E3<'Workings Model'!$P$10),Trip!F3*'Workings Model'!$Q$10%,"")&IF(AND(Trip!O3="E",Trip!E3>='Workings Model'!$O$11,Trip!E3<'Workings Model'!$P$11),Trip!F3*'Workings Model'!$Q$11%,"")&IF(AND(Trip!O3="R"),0,"")

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: To set limit to value generated in a range

    Hi,

    maybe you could exploit MIN function to set the limit


    =MIN(200, your formula)

    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    02-15-2017
    Location
    Cochin
    MS-Off Ver
    windows 8
    Posts
    3

    Re: To set limit to value generated in a range

    Hello, thanks for responding.
    I had tried it so, but i get parenthesis error for the last value if false in the formula.

  4. #4
    Registered User
    Join Date
    02-15-2017
    Location
    Cochin
    MS-Off Ver
    windows 8
    Posts
    3

    Re: To set limit to value generated in a range

    I did control shift enter, and if i accept the auto corrections, i get #value! and if i correct manually it gets to the last "value if false" in the formula
    Last edited by Vinosh; 02-15-2017 at 10:56 AM.

+ 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. If initials are generated in a cell then a permanent date stamp is generated
    By pageandrewr1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-08-2016, 12:15 PM
  2. [SOLVED] SUMPRODUCT where the range is generated by a function?
    By CakeMish in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-01-2015, 07:52 AM
  3. Replies: 4
    Last Post: 06-08-2014, 06:06 PM
  4. Replies: 5
    Last Post: 01-12-2014, 03:40 AM
  5. Setting up a dynamically generated range reference
    By cveetan in forum Excel General
    Replies: 8
    Last Post: 02-22-2012, 07:37 AM
  6. SUMIFS with User generated date range?
    By magicbob007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2011, 02:22 PM
  7. [SOLVED] Dynamically Generated Range using Macro
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2006, 03:15 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