Custom Function and Recalculate (2000 SR1A)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

That worked.
A very big thank you.

Reply | Quote

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:A16,LEN(A1:A16)-1)=””,8,RIGHT(A1:A16,LEN(A1:A16)-1)))))

press control-shift-enter to enter it.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thank you.

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

Tom

Reply | Quote

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.

Reply | Quote