UDF (like customised COUNT) ?

**Athanasios2104** · 11-17-2012, 05:47 PM

Hi all, i have a column of 8760 values (total hours of a year) with zeros and non-zero values. I want to count the duration of the zero values, that is the sequential entries of zero values (which represent the hours of zero production for the power plant). More precisely, i want something like a COUNT function, which will add +1 for every sequential zero entry, when it finds a non-zero entry it stops (prints the till-that-entry result in a cell) and starts over.

I have thought of a UDF (user defined function) that might work, but (1) i would like to hear if there's a faster/easier way and, if not, (2) what is missing from the code to work (i haven't used UDFs before).

Thanks in advace

Please Login or Register  to view this content.

**FDibbins** · 11-17-2012, 06:05 PM

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**Athanasios2104** · 11-17-2012, 08:01 PM

Thanks for the response; i think this can be solved without uploading any data.

For example, assume we have column A:

Please Login or Register  to view this content.

What i am looking for is a function to print in column B

Please Login or Register  to view this content.

meaning that in column A from top to bottom i have first 4 sequential zeros, then 1 zero, then 3 sequential zeros and so on.

**abousetta** · 11-17-2012, 08:47 PM

Maybe something like this:

Please Login or Register  to view this content.

It's not a UDF but it will accomplish the job.

abousetta

**jason.b75** · 11-18-2012, 06:04 AM

You could try an array formula instead, enter this formula in B2 and confirm it as an array with Shift Ctrl & Enter, then fill down as needed.

Formula:

Please Login or Register  to view this content.

**Athanasios2104** · 11-18-2012, 12:41 PM

@abousetta: i still use as module through vba though, correct?

@ jason: excel gives me "wrong formula" error when i try t

**abousetta** · 11-18-2012, 02:02 PM

Yes, standard module.

abousetta

**jason.b75** · 11-18-2012, 05:46 PM

Not sure what you mean by "wrong formula" error, you would need to translate the functions if you have excel installed in a language other than english, also regional settings might mean commas need to be changed to semicolons.

**Athanasios2104** · 11-18-2012, 05:52 PM

thanks for your answers guys, the solution my bro came up with is two simple IF functiosn and then finding min/max

the values in examination are in column C, so with the following in column D:

Please Login or Register  to view this content.

and the following in column E:

Please Login or Register  to view this content.

i get the desired values of sequential zeros.

/SOLVED

**abousetta** · 11-18-2012, 05:57 PM

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save