+ Reply to Thread
Results 1 to 27 of 27

adding to formula revised

  1. 10-15-2006, 02:36 AM #1
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182

    adding to formula revised

    how can i add -(q10:q11) to the following formula with out returning #VALUE in cell N9, n9 is where this formula is

    =IF(AND(ISBLANK(D9),ISBLANK(E9)),"",IF(24*(IF(E9>D 15,D15+2-E9,D15-E9))=0,24,24*(IF(E9>D15,D15+3-E9,D15-E9))))

    i have tried adding the following to the formula -(q10:q11)

    but does not work

    please download my work sheet to see what i mean

    q10 and q11 do have formulas in them.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-15-2006, 05:33 AM #2
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525
    My computer doesn't recognize the file format, save it as a regular .xls file then zip it
    ,
    Xlorate.com, Selection Codes, YouYube Channel
    Reply With QuoteRegister To Reply
  3. 10-15-2006, 05:43 AM #3
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    i opened it ok
    Reply With QuoteRegister To Reply
  4. 10-15-2006, 06:16 AM #4
    oldchippy
    oldchippy is offline
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Quote Originally Posted by Crasher
    i opened it ok
    Not everybody can open 2007 Excel files, especially if they have 2003
    Reply With QuoteRegister To Reply
  5. 10-15-2006, 06:30 AM #5
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    hi oldchippy nice to here from you again.

    is it possilbe that you can help me on this problem please.

    this is the very last thing that needs to be done on this worksheet and then it is complete.
    Reply With QuoteRegister To Reply
  6. 10-15-2006, 01:19 PM #6
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    can anyone help please
    Reply With QuoteRegister To Reply
  7. 10-15-2006, 01:20 PM #7
    oldchippy
    oldchippy is offline
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Can't open the zip file, Excel doesn't recognise it????
    Reply With QuoteRegister To Reply
  8. 10-15-2006, 02:08 PM #8
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    try it this time
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 10-16-2006, 02:12 AM #9
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    some one must be able to help surely
    Reply With QuoteRegister To Reply
  10. 10-16-2006, 03:52 AM #10
    oldchippy
    oldchippy is offline
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Quote Originally Posted by Crasher
    try it this time
    Still can't open it. What is an .xlsx file?
    Reply With QuoteRegister To Reply
  11. 10-16-2006, 04:14 AM #11
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by oldchippy
    Still can't open it. What is an .xlsx file?
    Hi oldchippy, it's an Excel Microsoft Office Open XML Format Spreadsheet file (http://filext.com/detaillist.php?extdetail=XLSX)

    Crasher, when you open this file, what are your 'Save As' options?
    ---
    Reply With QuoteRegister To Reply
  12. 10-16-2006, 11:45 AM #12
    mangesh_yadav
    mangesh_yadav is offline
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Do you want to add it manually or automatically through some code.
    If you want it to add manually, then do the following:

    In the formula in cell N9, add the part
    -(Q10:Q11)
    just before the last bracket to avoid #VALUE


    Mangesh
    Reply With QuoteRegister To Reply
  13. 10-16-2006, 03:35 PM #13
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    automaticly please that would help no end
    Reply With QuoteRegister To Reply
  14. 10-17-2006, 01:53 AM #14
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    anyone help please
    Reply With QuoteRegister To Reply
  15. 10-17-2006, 02:38 AM #15
    mangesh_yadav
    mangesh_yadav is offline
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    You should use:

    Range("N9").Formula = Left(Range("N9").Formula, Len(Range("N9").Formula) - 1) & "-(Q10:Q11)" & Right(Range("N9").Formula, 1)



    Mangesh
    Reply With QuoteRegister To Reply
  16. 10-17-2006, 11:44 AM #16
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    so what is the formula gonna look like please
    Reply With QuoteRegister To Reply
  17. 10-17-2006, 01:18 PM #17
    mangesh_yadav
    mangesh_yadav is offline
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    =IF(AND(ISBLANK(D9),ISBLANK(E9)),"",IF(24*(IF(E9>D 15,D15+2-E9,D15-E9))=0,24,24*(IF(E9>D15,D15+3-E9,D15-E9)))-(Q10:Q11))

    I hope that is what you want.

    Mangesh
    Reply With QuoteRegister To Reply
  18. 10-17-2006, 08:45 PM #18
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    does not work because as soon as you enter a start time in on the friday it displays #value
    Reply With QuoteRegister To Reply
  19. 10-18-2006, 03:31 AM #19
    mangesh_yadav
    mangesh_yadav is offline
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Ofcourse it is going to display the error. What is your intention of adding the
    -(Q10:Q11)
    You cannot simply subtract a range. That is what is giving you the error.

    Mangesh
    Reply With QuoteRegister To Reply
  20. 10-18-2006, 06:55 AM #20
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    did you by any chance download my work file because if you did you would have seen what i would like to do?!
    Reply With QuoteRegister To Reply
  21. 10-18-2006, 07:29 AM #21
    mangesh_yadav
    mangesh_yadav is offline
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    nope. I didn't.
    I usually don't download files.

    Mangesh
    Reply With QuoteRegister To Reply
  22. 10-18-2006, 08:25 AM #22
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    could you please as it would save alot of posts and you would see what it is im trying to do please.

    many thanks
    Reply With QuoteRegister To Reply
  23. 10-18-2006, 12:39 PM #23
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    this is such hard work
    Reply With QuoteRegister To Reply
  24. 10-18-2006, 05:38 PM #24
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Crasher
    this is such hard work
    not made any easier by the xlsx file, you need to 'saveAs' a standard file format if you want others to be able to read your file.

    Your formula =IF(AND(ISBLANK(D9),ISBLANK(E9)),"",IF(24*(IF(E9>D 15,D15+2-E9,D15-E9))=0,24,24*(IF(E9>D15,D15+3-E9,D15-E9)))) seems to say, If E&D9 are blank then blank, else if *24 = 0 then 24, else 24*

    If you want to subtract Q10:Q11 then why not either -Q10-Q11 or -Sum(Q10:Q11) to give

    =IF(AND(ISBLANK(D9),ISBLANK(E9)),"",IF(24*(IF(E9>D 15,D15+2-E9,D15-E9))=0,24,24*(IF(E9>D15,D15+3-E9,D15-E9))-Q10-Q11))

    or
    =IF(AND(ISBLANK(D9),ISBLANK(E9)),"",IF(24*(IF(E9>D 15,D15+2-E9,D15-E9))=0,24,24*(IF(E9>D15,D15+3-E9,D15-E9))-Sum(Q10:Q11))


    ---
    Si fractum non sit, noli id reficere.
    Reply With QuoteRegister To Reply
  25. 10-19-2006, 03:36 PM #25
    Crasher
    Crasher is offline
    Forum Contributor
    Join Date
    10-07-2006
    Posts
    182
    just what the doctor ordered many thanks everyone
    Reply With QuoteRegister To Reply
  26. 10-19-2006, 05:50 PM #26
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Crasher
    just what the doctor ordered many thanks everyone
    Good to see your problem resolved, . . and thanks for the response
    Reply With QuoteRegister To Reply
  27. 10-23-2006, 02:50 AM #27
    mangesh_yadav
    mangesh_yadav is offline
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Sorry, I was travelling and could not reply. Good to know your problem is solved.

    Mangesh
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1