+ Reply to Thread
Results 1 to 12 of 12

How to use logical functions with criterias which contain formulas?

  1. 02-13-2018, 02:29 PM #1
    thefallofrome
    thefallofrome is offline
    Registered User
    Join Date
    03-04-2016
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    15

    How to use logical functions with criterias which contain formulas?

    This is a formula I came up with but the logical test picks the incorrect criteria.

    =IF((AND(INT(($AM$155*O136)/5+0.499)*5>O100,INT(($AM$155*O136/5+0.499)*5<=O100)),O172,INT(((O100*1.05)/5+0.499))*5)

    I am trying to say, if statement 1 (formula #1 is greater than value #1 then O172) is true, and if statement 2 (formula #2 is less than or equal to value #2 then INT(((O100*1.05)/5+0.499))*5) is true.

    My problem is that if statement 1 is true, the formula will generate the value for statement 2, rendering my formula a failure.

    Please any help would be great,

    Rome
    Last edited by thefallofrome; 02-13-2018 at 02:49 PM.
    Reply With QuoteRegister To Reply
  2. 02-13-2018, 02:36 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to use logical functions with criterias which contain formulas?

    Maybe change the first > O100 to < O100
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 02-13-2018, 02:42 PM #3
    thefallofrome
    thefallofrome is offline
    Registered User
    Join Date
    03-04-2016
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    15

    Re: How to use logical functions with criterias which contain formulas?

    Quote Originally Posted by Richard Buttrey View Post
    Maybe change the first > O100 to < O100
    I tried that but it didn't work. I'm worried that perhaps my logical test function may not be correct.
    Reply With QuoteRegister To Reply
  4. 02-13-2018, 03:05 PM #4
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,654

    Re: How to use logical functions with criterias which contain formulas? 

    Please Login or Register  to view this content.
    This is always FALSE
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  5. 02-13-2018, 03:17 PM #5
    thefallofrome
    thefallofrome is offline
    Registered User
    Join Date
    03-04-2016
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    15

    Re: How to use logical functions with criterias which contain formulas?

    Quote Originally Posted by popipipo View Post 
    Please Login or Register  to view this content.
    This is always FALSE
    How would I construct the formula so it is not always FALSE?
    Reply With QuoteRegister To Reply
  6. 02-13-2018, 03:40 PM #6
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,654

    Re: How to use logical functions with criterias which contain formulas?

    Upload an excel file with values you expect to get
    Reply With QuoteRegister To Reply
  7. 02-14-2018, 12:24 AM #7
    Root_
    Root_ is offline
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    514

    Re: How to use logical functions with criterias which contain formulas?

    Is this what you need?

    =IF(INT($AM$155*O136/5+0.499)*5>O100,O172,INT(O100*1.05/5+0.499)*5)
    Reply With QuoteRegister To Reply
  8. 02-23-2018, 05:16 PM #8
    thefallofrome
    thefallofrome is offline
    Registered User
    Join Date
    03-04-2016
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    15

    Re: How to use logical functions with criterias which contain formulas?

    It didn't work Root, thank you. It's like this, if I have a formula that generates a result of 5. This 5 needs to beat another value which is 10 but it doesn't. So since my 5 is not true, I need to take the 10 and multiply it by 1.05 and this will be my new result.

    At the same time this value needs to be rounded to the nearest 5 (0-2.54 gets rounded down, 2.55-4.99 gets rounded up).

    This is too complex for me, smh
    Reply With QuoteRegister To Reply
  9. 02-23-2018, 07:23 PM #9
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to use logical functions with criterias which contain formulas?

    As popipipo said, upload the workbook and manually add the results you expect.
    Reply With QuoteRegister To Reply
  10. 02-25-2018, 12:48 PM #10
    thefallofrome
    thefallofrome is offline
    Registered User
    Join Date
    03-04-2016
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    15

    Re: How to use logical functions with criterias which contain formulas?

    I've attached the workbook. I appreciate all help. Thank you.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 02-25-2018, 02:20 PM #11
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to use logical functions with criterias which contain formulas?

    Hi,

    You didn't manually add the results so I'll assume you want the I:K formulae modifying.
    Try in I4 copied to I4:I7

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


    similarly for J:K
    Reply With QuoteRegister To Reply
  12. 02-25-2018, 03:35 PM #12
    thefallofrome
    thefallofrome is offline
    Registered User
    Join Date
    03-04-2016
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    15

    Re: How to use logical functions with criterias which contain formulas?

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    You didn't manually add the results so I'll assume you want the I:K formulae modifying.
    Try in I4 copied to I4:I7

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


    similarly for J:K
    This is brilliant! Thank you Richard!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. using logical functions
    By bennadelaidefringe in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-19-2015, 10:09 PM
  2. Using Logical Lookup functions
    By bwavering in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2014, 05:25 PM
  3. [SOLVED] Logical , functions , on ( IF , AND , OR)
    By The Learner in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-20-2012, 04:30 AM
  4. Confusing two logical functions
    By Digestive in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2011, 10:03 AM
  5. Logical Functions
    By be165 in forum Excel General
    Replies: 3
    Last Post: 09-09-2009, 04:08 AM
  6. [SOLVED] using logical functions
    By civilized_engr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-10-2006, 02:40 PM
  7. [SOLVED] logical functions
    By wiz546 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2005, 12:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1