Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting number of times value is greater than previous week
Need to be able to count the number of times the value is greater than the
previous week value in a row of numbers: 15, 13, 15, 14, 16, 17, 18 This would show a 4 as in for 4 weeks we were on an upward trend. Then have to do the reverse for downward trends - on the same row of numbers. 15, 13, 15, 14, 16, 17, 18, 15, 14, 13 say 3 weeks later this would show downward trend for 3 weeks. Could be 2 separate formulas. Any help would be appreciated - using a massive if statment right now that only allows me to go back about 6 weeks - so if the trend last longer than 6 weeks, I keep showing a 6 for the following weeks. Trying to stay away from macros if possible. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting number of times value is greater than previous week
If your data is in A1 thru G1, then in B2 enter:
=IF(B1A1,1,0) and copy across. The sum of row 2 is your answer. to get the other case use =IF(B1<A1,1,0) -- Gary''s Student "tereasajw" wrote: Need to be able to count the number of times the value is greater than the previous week value in a row of numbers: 15, 13, 15, 14, 16, 17, 18 This would show a 4 as in for 4 weeks we were on an upward trend. Then have to do the reverse for downward trends - on the same row of numbers. 15, 13, 15, 14, 16, 17, 18, 15, 14, 13 say 3 weeks later this would show downward trend for 3 weeks. Could be 2 separate formulas. Any help would be appreciated - using a massive if statment right now that only allows me to go back about 6 weeks - so if the trend last longer than 6 weeks, I keep showing a 6 for the following weeks. Trying to stay away from macros if possible. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting number of times value is greater than previous week
I could do this but I need to rate over 100 rows which have over 150 columns
(this method would double my rows in a 25M file) - so I really need to have one formula in one cell to reduce the complications. Any other ideas? "Gary''s Student" wrote: If your data is in A1 thru G1, then in B2 enter: =IF(B1A1,1,0) and copy across. The sum of row 2 is your answer. to get the other case use =IF(B1<A1,1,0) -- Gary''s Student "tereasajw" wrote: Need to be able to count the number of times the value is greater than the previous week value in a row of numbers: 15, 13, 15, 14, 16, 17, 18 This would show a 4 as in for 4 weeks we were on an upward trend. Then have to do the reverse for downward trends - on the same row of numbers. 15, 13, 15, 14, 16, 17, 18, 15, 14, 13 say 3 weeks later this would show downward trend for 3 weeks. Could be 2 separate formulas. Any help would be appreciated - using a massive if statment right now that only allows me to go back about 6 weeks - so if the trend last longer than 6 weeks, I keep showing a 6 for the following weeks. Trying to stay away from macros if possible. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting number of times value is greater than previous week
I am not so sure about your layout, are your weeks are extending
horizontally or vertically? Anyway, I tried the following formula in a single column of numbers, in cells A1:A17. Next to them I supplied formulas in B2:B17. This formula computes, next to any cell of the source data, the number of weeks with downward trend, including the current week. This is an array formula, thus it must be entered with Shift+Ctrl+Enter. To be entered in B2. =ROW()-MAX(ROW($B$2:B2)*($A$2:A2$A$1:A1)) You will have to change to < for upward trend. You might need to add/subtract a constant, if your data do not start in row 1. You might want to change all instances of ROW() to COLUMN() if time extends along the horizontal. A more robust formula can be probably built, but at the time this is the best I can come up with. HTH Kostis Vezerides |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting number of times value is greater than previous week
Assuming that A1:AZ1 contains your data, try...
Upward: =SUMPRODUCT(--(B1:AZ1A1:AY1)) Downward: =SUMPRODUCT(--(B1:AZ1<""),--(B1:AZ1<A1:AY1)) Hope this helps! In article , tereasajw wrote: Need to be able to count the number of times the value is greater than the previous week value in a row of numbers: 15, 13, 15, 14, 16, 17, 18 This would show a 4 as in for 4 weeks we were on an upward trend. Then have to do the reverse for downward trends - on the same row of numbers. 15, 13, 15, 14, 16, 17, 18, 15, 14, 13 say 3 weeks later this would show downward trend for 3 weeks. Could be 2 separate formulas. Any help would be appreciated - using a massive if statment right now that only allows me to go back about 6 weeks - so if the trend last longer than 6 weeks, I keep showing a 6 for the following weeks. Trying to stay away from macros if possible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
4 and 5 week months | Excel Discussion (Misc queries) | |||
Sigma Function? | Excel Worksheet Functions | |||
Changing cell or text color depending on week number | Excel Discussion (Misc queries) | |||
Counting the number of times a word appears in a worksheet | Excel Worksheet Functions | |||
Can the number of times undo is used in Excel 2002 be increased? | Setting up and Configuration of Excel |