Formula using greater than and less than for if then statement

[STemp]

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.

[ChemistB]

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???

[STemp]

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

[STemp]

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)

[ChemistB]

Assuming you don't have fractions or negative numbers

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)

[STemp]

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)

[STemp]

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

[GeoffW283]

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

[STemp]

Sorry if that attached twice. Here is a couple of samples of what is happening. Below are the parameters I need:

1. 80 hour period. Vacation, sick and holiday cannot push work hours over 80.
2. Holiday hours count toward making up 80 hours. Any holiday hours that push work hours over 80 should be remainder at bottom of holiday column.
3. The vacation and sick columns should only show hours that push work hours to 80 at bottom of respective columns. (ie. if only 6 vacation hours are needed to reach 80 work hours and 10 hours were put in, only 6 should show at bottom of vacation column).

[GeoffW283]

How can you have 10 hrs of holiday on a line like row 3 that shows clock in/out times and 10 hours worked?

In I17 then copied to the other 2 examples:

Please Login or Register  to view this content.

This yields your expected result for your 3 provided examples. I'm not sure whether this is exactly what you want. If not please provide additional counter examples.


As a side note the following formulas do the same thing as yours, I think, but are somewhat simplified.

In F18 (just simplified):

Please Login or Register  to view this content.

In F19 (just simplified):

Please Login or Register  to view this content.

Let me know if this works for you or if additional changes are needed.

[STemp]

Geoff- Unfortunately working holidays is part of the job. Accounting for that so the pay is appropriate is one of the bigger parts of this task that I'm trying to make sure I get right. Your suggestion for I17 worked great. Also made the other suggestions as cleaner is better. I'm going to play with a few variations to see if this did the trick. If not, I will look for help again. Thank you very much.

STemp

[STemp]

OK, not quite fixed. Hoping for this to be a master time sheet where some periods do not have holidays. Have put several examples in where the previous fix didn't quite get what is needed.

[GeoffW283]

Thanks for the reputation points despite the less-than-complete fix

I replaced your G17 and H17 formulas with simple column sums for Sick and Vac.
In G17 and the equivalent cells for the second and third examples:

Please Login or Register  to view this content.

In H17 and the equivalent cells for the second and third examples:

Please Login or Register  to view this content.

Then in I17 and copied to the other 2 examples:

Please Login or Register  to view this content.

This gives your expected results for the 3 examples in your post #12 attachment. I also checked that the 3 examples in your post #9 still work.

Keep testing, I'm not convinced we have covered all cases!

[STemp]

Geoff, again thank you for all of your help. This still didn't quite work as I was trying to account for someone accidentally putting vacation or sick time down and not realizing that they did not need it to make the 80 hours. Was hoping to have it show 0s at the bottom of the columns so that if someone in payroll wasn't paying attention they didn't lose the vacation or sick time they didn't need to take. BUT, it has become a moot point now as I learned today that the payroll department has decided to go with a $30k software program instead. Again, thanks for your help. Good to know that I have a place to go to get good help with this type of thing when I need it.

STemp

[GeoffW283]

That's a shame. A bit more polishing and you could have saved your company $30K.
Anyway, thanks for the feedback and for marking this as solved.