need help with IF formula - depending on which column has information entered.

[michael25818]

Hi & Thanks in advance for your help!!

Attached is a copy of my excel spreadsheet. I have all the formulas worked out already but just added two new columns. I want to use the IF formula for columns H & I. The total is calculated in Column J. If there are any values entered in those columns, nothing should be entered in the "work/lunch" times/columns. Basically, either the employee has a work day or a fitting day. The IF formula would be in the K column. Regardless of what's entered in H & I, the minimum number of hours should be 1. After that, it should be calculated in 15 minute increments. The $/rates breakout I have listed in K13 - K19 would still apply. I hope I explained myself correctly but sorry if I didn't. Would be more than happy to answer any questions & appreciate all the help!!

[teylyn]

Hello,

this is a bit confusing. You say you want an IF formula for columns H and I and then you say the IF formula should be in column K. There is already a formula in that column. Do you want to replace it?

It would be a lot easier if you could put in some data and manually enter the expected result. Then explain the logic that leads to these results.

cheers, teylyn

[michael25818]

I can't really enter the values in the spreadsheet because I don't want to lose the previous formula. This is a timecard to track an employees work hours. If they come in and work only a FITTING day, that's all they, get no other work time. The minimum they are suppose to receive for that FITTING day is 1 hour of their negotiated amount. After that hour, they will be paid in 15 minute increments. I created Column J to track total number of hours work based on In & Out times for the Fitting Day. I need Column K to translate that into 1 hour minimum, then 1.25, 1.5, 1.75, etc. Is it possible to have a formula in Column K to use the current formula for Column's B - G & another formula for Column's H - J?

[FDibbins]

I can't really enter the values in the spreadsheet because I don't want to lose the previous formula.

save the file under a different name, then change that 1. If they are the same format/layout, you should not have a problem copying the formulas over

[michael25818]

I went ahead and showed an example of what I want the new formula to do in Row 4 (Fitting Day) and what a work day looks like on Row 5. I need the formula to be updated in Column K. Thanks in advance for all your help and patience.

[teylyn]

OK, so you want to use one formula if cells B and C have values, and another formula if cells H and I have values. One possible way to do this is

=IF(COUNT(B4:C4)=2,CEILING(ROUNDUP(MIN(8,G4),0),8),IF(COUNT(H4:I4)=2,I4-H4,0))

If there are values in both B4 and C4, then do the ceiling formula. Otherwise, if there are values in both H4 and I4, do another calculation. Otherwise, return 0.

You may want to adjust the formula you want to use for the fitting thing.

cheers, teylyn

[michael25818]

Hi Teylyn, this is almost exactly what I'm looking for. Thanks so much! I would only like to see if you can still assist me with figuring out how to change the formula for H4 & I4. I would like to have a minimum total of 1 in K4 every time any number is entered in H4 & I4. I would also like the total to increase in .25, .50, .75, etc increments.

[teylyn]

try

MAX(1,CEILING((I4-H4),0.25))

By the way, are the Orange values meant to be times? I.e. is 13.50 meant to be 1:50 pm? If so, you need to revise your structure, because these are not time values. It may work fine for full hours, but from 12:00 to 13:50 (as in ten to two) is not 1.5 hours.

[michael25818]

Hi Teylyn, Thanks for the new formula! I've entered this in "J" and it works just like I would like it except when nothing is in H or I, the value appears as 1. Can I have it read 0? Also, with me entering this formula in "J", I have to update the formula in "K". All I need in "K" is to have the IF formula to be revised to either pick up the times from B - G or the number in "J". The current formula is =IF(COUNT(B4:C4)=2,CEILING(ROUNDUP(MIN(8,G4),0),8),IF(COUNT(H4:I4)=2,I4-H4,0)). How would I change the time structure? Thanks again!!!

[teylyn]

To use in J4 and copied down

=IF(COUNT(H4:I4)=2,MAX(1,CEILING((I4-H4),0.25)),"")

[michael25818]

Hi, so I went ahead and updated the spreadsheet with the new formula in Column "J". I'm still having the same problem as before. By using this formula, column "J" comes up with the correct amount but column "K" doesn't. Can't I adjust the formula in column "K" to use the original formula or IF nothing is entered in "B - G", use the number that's in column "J"? I've attached a copy of the spreadsheet again and this time I'm using both formula's you provided in "J". Row 4 has values entered and "K" doesn't match up. Row 5 is blank and "J" has 1 in it. Row 6 has the same problem as Row 4. Lastly Row 7 doesn't have 0.00 when blank. Thanks again!!

[teylyn]

Just reference column J in the formula in column K instead of doing the calculation all over again.

[michael25818]

That's the formula I'm having trouble with. The current formula is =IF(COUNT(B4:C4)=2,CEILING(ROUNDUP(MIN(8,G4),0),8),IF(COUNT(H4:I4)=2,I4-H4,0))
Should I be changing the last portion of the formula? Thanks!

[teylyn]

If J4 contains the correct calculation that takes into account H4 and I4, then you don't need to calculate that again. Compare:

=IF(COUNT(B4:C4)=2,CEILING(ROUNDUP(MIN(8,G4),0),8),IF(COUNT(H4:I4)=2,I4-H4,0))

=IF(COUNT(B4:C4)=2,CEILING(ROUNDUP(MIN(8,G4),0),8),J4)