User defined function to calculate difference

**antonymiller** · 06-16-2010, 02:52 PM

Thanks guys, I figured it out and you helped a bunch.

Hi All I'm having difficulty getting a custom formula to calculate the difference between the value of cells in adjacent columns.

Specifically, I am tracking the difference between "BeginTime" represented in column A and EndTime in Column B. The "Difference" between the two is calculated in Column C.

Please Login or Register  to view this content.

works just fine, and I can drag that formula down as far i want. So I wouldn't really need a custom function but I do not want the consequential value of "0" that appears cells that aren't used yet.

The following code does absolutely nothing and I'm stuck on how to fix it. any suggestions are appreciated.

Please Login or Register  to view this content.

**DonkeyOte** · 06-16-2010, 02:54 PM

UDF's are generally slower than native functions.

Couldn't you just use a pre-emptive COUNT test ?

=IF(COUNT(A2:B2)<2,"",B2-A2)

(depending on the nature of the values in A2:B2 you might need to adjust your difference calculation)

**royUK** · 06-16-2010, 02:55 PM

You shouldn't need code,use an If function to check if a cellis used

=IF(ISBLANK(A1),"",A1-B1)

**antonymiller** · 06-16-2010, 03:19 PM

Thanks Donkey and royUK,

I had already gone that route.... the problem is when I drag these formula's down the length of the "Difference" column, the unused cells display a value of 0.00 whereas i would prefer that they simply remain blank. Getting them to remain blank after the calculation is thing i'm struggling with. My bad, I should have been more clear on that.

Thanks again.

**DonkeyOte** · 06-16-2010, 03:41 PM

You've marked this Solved yet final post implies unresolved ?

FWIW, in the case of the COUNT ex. you would only get a numeric output if both start & end are complete (use of COUNT assumes valid (date)Time entries of course).