+ Reply to Thread
Results 1 to 4 of 4

2 formulas in one cell

  1. #1
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    2 formulas in one cell

    is it possible to put two formulasinto one cell?

    On the attached timesheet there are columns IN, OUT, IN,OUT
    The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

    looking at cell 'C11 ' in the attached worksheet, I currently have the formula
    =IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00
    Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.
    The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success
    Thanks

    =IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)
    Attached Files Attached Files
    Last edited by nje; 08-11-2010 at 03:10 PM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    Belgium
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,198

    Re: 2 formulas in one cell

    Hi
    try =IF(OR(C13=0;C9=0);"";(C9-C8))

    BTW what is sum(c9-c8) ? c9-c8 will do nicely I think
    Quoting entire posts clutters the forum and makes threads hard to read !

  3. #3
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: 2 formulas in one cell

    hello Arthurbr, Thanks,excellent solution, although it didn't work at first.
    Were you testing me to see if I was paying attention, I was and luckily I spotted that you used semi colons instead of commas. Thanks again very much appreciated

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    Belgium
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,198

    Re: 2 formulas in one cell

    You're welcome. I'll have to change my settings to use comma's in the future. Thx for reminding me. ANd don't forget to eliminate unnecessary SUM statements, they cluttre the formula

    sum(cell1:cell2) is correct as is sum( cell1,cell1,cell3)
    sum( cell1-cell2) has an unnecessary sum function cell1-cell2 is OK

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

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