Trying to add to an existing formula to leave cell blank depending on value of another cel

My boss is under the impression that I am an EXCEL expert, but I'm really close to proving him wrong. I have made a bunch of changes to an old spreadsheet for him, but I am stuck on the final bit. I need to add to a couple of formulas to cause them to return a blank cell based on the value of another cell. I have tried every iteration that I can think of but continue to fail. I have attached a copy of the sheet.

The problem is that one of the sheets is looking for time values to sum and all of the extra data is really throwing off the numbers.

Example:

=IF($B$24<>0,$B$24+TIME(0,L$85,0),"")
I would like to add a formula to return a blank cell if L6 is either blank or contains a zero.

The same with this one: =IF($B$12>1,IF(L$7<>0,L7+TIME(0,$B$15,0),""),"")

I feel like this should be simple to do, but my brain is now officially fried.

Any assistance would be greatly appreciated.

Hi TK720,

Not sure I understand what you're getting at, because row 6 looks like it will always be blank.

However, I CAN give you a clue!

Your formula is looking at the 13 minutes in B15, but this is entered as a number 13. You need to enter it as minutes ie 0:13 for any arithmetic to work.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

In fact, the row 6 cells are meant to enter a number to alter the times. There are some simple formulas around row 85 (might be hidden) which convert the time into minutes. The problem is that I need the cells with the above formulas to be blank
if the coresponding row 6 cell is empty.

I actually have a solution, but it is ugly. If I do not find a better solution I will instruct those who will use this sheet to enter a particular letter into the corresponding row 6 cell and add some conditional formatting to hide all of the #VALUE! responses. I'd much rather learn the correct way to do it though.

OK, Sorry, I should have looked closer.

=IF(L$6<=0,"",IF($B$12>ROW()-7,IF(L$7<>0,L7+TIME(0,$B$15,0),""),""))

I just looked at Column L. In L8 I entered the above formula, and copied it down. All cells are now blank.

I just wrapped an IF statement around your formula which tested for L6 being less than or equal to zero.

I made one other change to make copying down possible. ROW()-7 returns 1 on row 8, 2 on row 9 , 3 on row 10 etc. This avoids having to enter the formula every time.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".