 SOLVED

# Formula won't complete calculations without all data

I am creating a spreadsheet that calculates tips based on hours worked and have a table with both AM and PM shifts with the below formula- (Total AM tips(L5)/Total AM hours(I32))*Employee's hours worked (I12) + (Total PM tips(L6)/Total PM hours(J32))*Employee's hours worked(J12)

=IFERROR(((\$L\$5/\$I\$32)*I12)+((\$L\$6/\$J\$32)*J12),0)

I am not getting an error message, but am not getting any data unless I have hours worked in both the AM Hours (I) & PM Hours (J) columns. I would like the formula to calculate even if there is no data for the AM formula - as we are not always open for lunch and would like to be able to leave cells blanks on those days.

2 Replies
best response confirmed by Katie830 (New Contributor)
Solution

# Re: Formula won't complete calculations without all data

you won't get an error because of the IFERROR but without it you would get a DIV BY 0 error. So you could break the IFERROR up: =IFERROR((\$L\$5/\$I\$32)*I12,0)+IFERROR((\$L\$6/\$J\$32)*J12,0)
But it would be better practice to avoid IFERROR so when there is a problem you well know there is a problem. So maybe try: =IF(\$I\$32=0,0,(\$L\$5/\$I\$32)*I12)+IF(\$J\$32=0,0,(\$L\$6/\$J\$32)*J12)

# Re: Formula won't complete calculations without all data

Thank you so much! This fixed the problem. I feel like I have been on an Excel crash course- good to know not to rely on IFERROR.