Calculating Alternating Values

**squiggler47** · 03-06-2009, 11:02 AM

I have a bunch of start End times in columns calculating total time :-

Please Login or Register  to view this content.

Which works great except is there an easy way to adapt this incase someone inserts a column and thevalues start in an even row?

Or is there a better way?

**NBVC** · 03-06-2009, 11:11 AM

Does this fix it?

Please Login or Register  to view this content.

**squiggler47** · 03-06-2009, 11:18 AM

Almost took the -1 out of both :-

Please Login or Register  to view this content.

I got a little more complicated, but i figured somewhere along the line I was over thinking the problem!

My stab :-

Please Login or Register  to view this content.

**shg** · 03-06-2009, 11:19 AM

That's a strange formula. It doesn't sum the elapsed times between start time in odd columns and end times in even columns; what does it do?

**squiggler47** · 03-06-2009, 11:21 AM

Originally Posted by shg

That's a strange formula. It doesn't sum the elapsed times between start time in odd columns and end times in even columns; what does it do?

it totals all the end times and subtracts the total of all the start times giving a total time in 1 formula for a whole week of morning afternoon sessions

**shg** · 03-06-2009, 11:49 AM

it totals all the end times and subtracts the total of all the start times giving a total time in 1 formula for a whole week of morning afternoon sessions

I don't think so:

Please Login or Register  to view this content.

That's your formula in A3.

**squiggler47** · 03-06-2009, 11:53 AM

its working fine on my sheet?

**shg** · 03-06-2009, 12:01 PM

Well, then, you're good to go

**squiggler47** · 03-06-2009, 01:26 PM

As a side note i did some more experimenting and some testing :-

Fastest, pain to modify

Please Login or Register  to view this content.

Second fastest but gives wrong result if the column gets changed

Please Login or Register  to view this content.

Third fastest and best since it uses the minimum of cell references/easiest to maintain

Please Login or Register  to view this content.

4th fastest UDF

Please Login or Register  to view this content.

5th fastest

Please Login or Register  to view this content.

6th Fastest

Please Login or Register  to view this content.

7th Fastest

Please Login or Register  to view this content.

Slowest

Please Login or Register  to view this content.

**shg** · 03-06-2009, 02:11 PM

How about

=SUMPRODUCT(I3:AJ3 * -1 ^ (COLUMN(I3:AJ3) + COLUMN(I3) + 1))

**squiggler47** · 03-07-2009, 05:27 AM

=ABS(SUMPRODUCT((1-ISODD(COLUMN(J3:AK3))*2)*J3:AK3))

its the second fastest and has the least references

yes yours was faster, then I thought why not this:-

Please Login or Register  to view this content.

it turns out this is actually faster than the straight addition by 7%?

and only has 2 sets of cell references!

**DonkeyOte** · 03-07-2009, 05:48 AM

shg, I like that method a lot... !

Traditionally you would see:

MOD(COLUMN(x),1) = 1/0

used to determine ODD/EVEN in preference to ISODD & ISEVEN given they require the Analysis ToolPak (not so much an issue in 2007)

shg's -1^COLUMN is a very elegant approach IMO

so kudos shg... one to be stored away...