I have been thinking of this but cant figure it out:
For example I have a Col start from row 10 (B10) going down. The number of row can be variable, but it should be odd number, the last row will not be count. Now I want the SUM of the subtraction of every 2 cell next together. Ex: I have B10:B21 then I need (B2-B1)+(B4-B3)+(B6-B5)+...+(B20-B19). How can I formular this?
Tkss!
I'm not exactly sure what you ultimately want to do, but...
This regular formula returns the sum of even_number_row items less the previous odd_number_row items
Example: (B2-B1)+(B4-B3)+(B6-B5).....(B41-B40)
With B1:B4 containing these values
=SUMPRODUCT((MOD(ROW(B2:B41)+1,2)*B2:B41)-(MOD(ROW(B1:B40),2)*B1:B40))
That formula returns: 1111 2 10 20 100 200
=(2-1)+(20-10)+(200-100)
Is that something you can work with?
Hi vietdieu,
See if the attached with a helper column isn't what you are looking for.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I have an update every day, every two row will be Stop and Start time (the number of Start/Stop will be varied), the last row will be the time that the machine is released, so I just want to sum the total time the machine working (every time from Start to Stop). Your formula is almost there except it still count the last row which is I will use it in different purpose. Can you adjust it a little bit.
Note: the total row will be totalrow= =COUNTIF(B10:B100,"<>"&"") Therefore the formula will start from B10 to B(totalrow-1).
Am I clear?
Tkss again,
For a single formula approach....
This regular formula returns the sum of StopTimes - StartTimes
where time entries begin on B10
It subtracts B10 from B11, B12 from B13, etc.=SUMPRODUCT(SIGN(1-MOD(ROW(B10:B101)+1,2)*2)*B10:B101)
Is that something you can work with?
Hi vietdieu,
If the number of rows changes, have you looked at Dynamic Named Ranges?
http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.beyondtechnology.com/geeks007.shtml
I will give you a better ex, From B10 I have (1,2,3,4,5,6,34) so I will have the sum=3 (the last 7 will be used for different purpose, because its released time)
The next day I have (10,15,20,25,30,35,40,45,99) so the sum=20 (the last 99 not use)
Your first formula is almost good except it still count the last cell.
Assuming your Start/Stop times begin in cell B10 and continue, in pairs, down the column
AND
the last numeric cell will be ignored by the summary function...
try this regular formula:
Does that help?=SUMPRODUCT(SIGN(1-MOD(ROW(B10:INDEX(B:B,LOOKUP(10^99,B:B,ROW(B:B))-1))+1,2)*2)*B10:INDEX(B:B,LOOKUP(10^99,B:B,ROW(B:B))-1))
That work excellent, but I will look into this to understand it. Many thanks!!!!
This is a little bit complicated for me, let's say B has the date and C has the time (both start from row 10) now I want to calculate total time of Stop and Start I have to sum up those two like this and this is way too long:
=SUMPRODUCT(SIGN(1-MOD(ROW(B10:INDEX(B:B,LOOKUP(10^99,B:B,ROW(B:B))-1))+1,2)*2)*B10:INDEX(B:B,LOOKUP(10^99,B:B,ROW(B:B))-1)) + SUMPRODUCT(SIGN(1-MOD(ROW(C10:INDEX(C:C,LOOKUP(10^99,B:B,ROW(C:C))-1))+1,2)*2)*C10:INDEX(C:C,LOOKUP(10^99,C:C,ROW(C:C))-1))
Is this more manageable?
It sums Col_B and Col_C values in one step at the end of the formula.=SUMPRODUCT(SIGN(1-MOD(ROW(B10:INDEX(B:B,LOOKUP(10^99,B:B,ROW(B:B))-1))+1,2)*2) *(B10:INDEX(B:B,LOOKUP(10^99,B:B,ROW(B:B))-1)+C10:INDEX(C:C,LOOKUP(10^99,B:B,ROW(B:B))-1)))
Note: If an end time can be on a different day than its corresponding start time, there will be issues and the formula will become more complicated.
Does that help?
Well it work perfectly! Both of them returned the same result but the last one is much shorter. Tks for your help!!!!!!!!!
You're very welcome...I'm glad you got something you could work with.
I had a problem here That is your note, the time is exceed one day (the Stop day can be any day after the Start day) the formula not count on it.
Actually, I think the formula I posted works just fine....Originally Posted by vietdieu
try setting the formula cell's format to show hours greater than 23
CTRL+1...Category: Time...Type: 37:30:55 (Which is equivalent to a custom format of: [h]:mm:ss;@)
Does that display correct results for you?
You'r right, thanks and sorry for bothering you with that silly thing
You'r right, thanks and sorry for bothering you with that silly thing
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks