+ Reply to Thread
Results 1 to 8 of 8

Formula using greater than and less than for if then statement

  1. #1
    Registered User
    Join Date
    06-13-2019
    Location
    United States
    MS-Off Ver
    Microsoft Office Proffesional 2013
    Posts
    5

    Formula using greater than and less than for if then statement

    Hello,

    I'm new to the forum. I'm trying to get a nested if then statement where one of the conditions includes a value that accounts for a range greater than 1 but less than 80. What I have so far (the and statement doesn't work, but shows what I need):

    =IF((F10:F23)=0,0),IF((F10:F23)>=80,SUM(I10:I23)),IF((F10:F23)>1and<80,SUM(F10:F23)+SUM(G10:G23)+SUM(H10:H23)+SUM(I10:I23)-80)


    Any help would be appreciated.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,358

    Re: Formula using greater than and less than for if then statement

    Try
    =IF((F10:F23)=0,0),IF((F10:F23)>=80,SUM(I10:I23)),IF((F10:F23)>1and<80,SUM(F10:F23)+SUM(G10:G23)+SUM(H10:H23)+SUM(I10:I23)-80)

    You can't put IF F10:F23 = 0. Does that mean you want them all to be 0 or if any of them are 0 or if their sum = 0??? Same with 80. Is "land" a named constant or cell reference or range???
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-13-2019
    Location
    United States
    MS-Off Ver
    Microsoft Office Proffesional 2013
    Posts
    5

    Re: Formula using greater than and less than for if then statement

    Sorry, didn't mean to make it confusing. If the F column adds to zero, I need the column the formula is in to show zero. If the F column adds to somewhere between 1 and 79 (should have been using 79 instead of 80) then it needs to add the F column sum along with the sum of other columns, subtract 80 and give a value. It's not supposed to be "land" but 1 and 80(79).
    Last edited by STemp; 06-13-2019 at 04:22 PM.

  4. #4
    Registered User
    Join Date
    06-13-2019
    Location
    United States
    MS-Off Ver
    Microsoft Office Proffesional 2013
    Posts
    5

    Re: Formula using greater than and less than for if then statement

    Here is the corrected number, but I still need to figure out how to get the if then to give a value if the sum of F column is 1 to 79:

    =IF((F10:F23)=0,0),IF((F10:F23)>=80,SUM(I10:I23)),IF((F10:F23)>1and<79,SUM(F10:F23)+SUM(G10:G23)+SUM(H10:H23)+SUM(I10:I23)-80)

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,358

    Re: Formula using greater than and less than for if then statement

    Assuming you don't have fractions or negative numbers

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


    As an aside, (since you really didn't need it for this problem) but
    AND and OR are placed first followed by the various arguments in parenthesis separated by commas (or semicolons depending on your location)
    so a and b and c = AND(a,b,c)
    a or b or c = OR(a,b,c)
    So you wanted
    AND(SUM(F10:F23)>=1, SUM(F10:F23)<=79)

  6. #6
    Registered User
    Join Date
    06-13-2019
    Location
    United States
    MS-Off Ver
    Microsoft Office Proffesional 2013
    Posts
    5

    Re: Formula using greater than and less than for if then statement

    Sorry, that leaves out one of the variables I need to account for. SUM(f)=0,0. SUM(f)=80,SUM(F). SUM(F)>=1 ? <80,SUM(F)+other sums. With your suggestion is looks like this, but still does not give a value:

    =IF((F10:F23)=0,0),IF((F10:F23)>=80,SUM(I10:I23)),IF((F10:F23)AND(SUM(F10:F23)>=1,SUM(F10:F23)<80) ,SUM(F10:F23)+SUM(G10:G23)+SUM(H10:H23)+SUM(I10:I23)-80)

  7. #7
    Registered User
    Join Date
    06-13-2019
    Location
    United States
    MS-Off Ver
    Microsoft Office Proffesional 2013
    Posts
    5

    Re: Formula using greater than and less than for if then statement

    Sorry, I think I'm just making it a bit too confusing. Let me post the equation I have and explain what I need.

    =IF(SUM(F10:F23)+SUM(G10:G23)+SUM(H10:H23)+SUM(I10:I23)>80,(SUM(I10:I23)),IF(SUM(F10:F23)+SUM(G10:G23)+SUM(H10:H23)+SUM(I10:I23)<80,SUM(I10:I23),IF(SUM(F10:F23)+SUM(G10:G23)+SUM(H10:H23)+SUM(I10:I23)=80,0)))

    If this helps this is for a 80 hour 2 week time sheet that includes work hours, vacation hours, sick hours and holiday hours. The only thing this equation does not account for is when there are work hours that sum from 70.5 to 79.5 with 10 or more holiday hours. When that happens, I need the equation to subtract the amount of the 10 (or more) holiday hours from the work hours and show the remainder. So, if the work hours add to 75, the holiday hours should only show 5 (the other 5 hours were used to complete the 80 hour work period).

  8. #8
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    859

    Re: Formula using greater than and less than for if then statement

    Welcome to the forum

    This is the first mention of 70.5 - 79.5 needing to be treated specially.

    Which of cols F thru I map to Work, Vac, Sick, Hol ??

    This would go a lot faster if you could upload a workbook (not a screenshot) with two or three examples where you have populated cells F10 through I23 and then populated your expected hand-calculated result(s) in your expected result cell(s). You can put each example on a separate worksheet so that rows/cols still match your description.

    To attach a workbook, click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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