# Formula using greater than and less than for if then statement

1. ## 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. ## 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???

3. ## 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).

4. ## 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. ## Re: Formula using greater than and less than for if then statement

Assuming you don't have fractions or negative numbers

Formula:
`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. ## 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. ## 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. ## 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”.

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