Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Function and Recalculate (2000 SR1A)

    I have created a function that looks at a range of cells and counts if the cells have an H in them and multiplies them by 8 (8 hours), but it also looks a cells like H6 and adds the 6 to the accumulated hours.
    For example, A1:A5 have this data in there cells respectively H,H2, , ,H this would give the result of 18 two 8 hours days one 2 hour day. This works okay if all the data is entered in the cells and the function is then placed on the sheet. But if the user were to come back and put another H in cell A3 the formula does not recalculate unless I re-enter the function.

    Is this something I have setup incorrectly in the function? or where it's placed. Currently saved in module one of the worksheet.

    Thanks for the help in advance

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function and Recalculate (2000 SR1A)

    Is the range where the data is being entered passed as a parameter to the function? If not, then add a range parameter to the function and pass the range, even if the function does not use the parameter.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function and Recalculate (2000 SR1A)

    If your data are in cells A1:A16, this array formula calculates the sum you described:

    =SUM(IF(LEFT(A1:A16,1)<>"H","",VALUE(IF(RIGHT(A1:A 16,LEN(A1:A16)-1)="",8,RIGHT(A1:A16,LEN(A1:A16)-1)))))

    press control-shift-enter to enter it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function and Recalculate (2000 SR1A)

    I tried copying in the formula you suggested only changing the A1:A16 to b16:af16 to reflect the range of values. I received #VALUE error. When trying to find out what was wrong I tried to do a countif using part of the formula and this is where I encountered the error:
    =countif(left(b16:af16,1),"H") this should count both H or H3 but instead I get a #Value
    When I tried this it worked:
    =countif(b16:af16,"H") this counted only the cells wih H. Is the problem using a range with the left function?

    Thanks in advance

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function and Recalculate (2000 SR1A)

    That worked.
    A very big thank you.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function and Recalculate (2000 SR1A)

    Select the cell with the formula as I wrote it, then press F2 (edit) or double click it, then in stead of pressing enter, press control-shift-enter.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function and Recalculate (2000 SR1A)

    Thank you.

    By the way, what does the CTRL + SHIFT + ENTER do? I noticed it put { at the beginning and } at the end.

    Tom

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Function and Recalculate (2000 SR1A)

    Ctrl+Shift+Enter makes a formula into an array formula. Array formulas are surrounded by { and }, but you shouldn't type those yourself - you'll get an error message if you do.

    Array formulas are very powerful. You can do incredible things with them, but unfortunately, they can be difficult to understand.

    If you do a search for "Array formula" (entire phrase) on this forum, you'll find lots of examples and links.

    One small example:
    Say you have two columns of numbers. You want to divide each number in the first column by the corresponding number in the second column, and add the results.
    The "standard" way would be to create a third column with the quotients, and then put the sum in another cell.
    But you can omit the intermediate column by calculating the som of the quotients in an array formula. Say your numbers are in A1:A20 and B1:B20. Enter the following formula in a cell:
    =SUM(A1:A20/B1:B20)
    and confirm with Ctrl+Shift+Enter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •