Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count function
Trying to figure out what you want, I screwed up the first formula by
putting in ABS and forgot to chang it back. You apparently, based on your multiposts of similar messages to misc and worksheet.functions, want A3-A2, A4-A3, etc =SUMPRODUCT((A2:A50-A1:A490)*(A1:A49<"")*(A2:A50<"")) will give you the count =SUMPRODUCT((A2:A50-A1:A490)*(A1:A49<"")*(A2:A50<"")*(A1:A49-A2:A50)) will give you the sum of the positive differences. This includes A2-A1, if you don't want that then adjust the formula as follows: =SUMPRODUCT((A3:A50-A2:A490)*(A3:A50<"")*(A2:A49<"")) -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... both A2-A3 and A3-A2 can not be 0. I assumed you meant A2-A30, A3-A40, A4 - A50, etc. so you would get a count of all cases where there was a decrease from one cell to the next. =SUMPRODUCT((abs(A1:A49-A2:A50)0)*(A1:A49<"")*(A2:A50<"")) will give you the count =SUMPRODUCT((A1:A49-A2:A500)*(A1:A49<"")*(A2:A50<"")*(A1:A49-A2:A50)) will give you the sum of the decreased amounts. I get 11 instances with a sum of 4.82 -- Regards, Tom Ogilvy "Omer" wrote in message ... How can I make Excell count the difference between subsequent rows and do two things Data at the end 1. Tell me how many times the value as a result of subtraction (A2-A3, A3-A2, etc) was 0 2. What is the Sum of those 0 Expected result: Based on this data there were 5 instances where the vaule was 0 and the sum is $$$ Thanks 58.63 58.27 58.66 59.21 59.73 59.57 59.43 60.38 60.64 60.37 61.14 61.63 61.46 60.75 60.85 59.93 59.35 59.70 59.05 59.20 58.88 58.34 59.74 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count function | Excel Worksheet Functions | |||
COUNT function | Excel Worksheet Functions | |||
count function | Excel Discussion (Misc queries) | |||
Count If Function | Excel Worksheet Functions | |||
count function | Excel Worksheet Functions |