Goal is to identify the trend for only the last 3 months as months are added
value 1,2,3 is “Up” — value 3,2 1 is”Down” — anything mixed like 3,5,2 is blank. They currently “eyeball” 100 rows to find the trend for each row
The offset formula works great as the monthly values are added except when there is a zero or a blank. How do I make it work with a zero as the value for a month?
A B C D E F G H I J K L M
1) Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2) 5 3 6 8
To Capture the Data
B4)Current Month 8 =OFFSET($B$2,0,COUNT($B$2:$M$2)-COUNTIF($B$2:$M$2,0))-1))
B5)Prior Month 1 6
=(OFFSET($B$2,0,(COUNT($B$2:$M$2)-COUNTIF($B$2:$M$2,0))-2))
B6)Prior Month 2 3
=(OFFSET($B$2,0,COUNT($B$2:$M$2)-COUNTIF($B$2:$M$2,0))-3))
To Determine the Trend
UP
B9) 1 =IF(B4>B5,1,0)
B10) 1 =IF($B$5>$B$6,1,0)
B11) 2 =SUM(B9:B10)
DOWN
B14) 0 =IF($B$4<$B$5,10,0)
B15) 0 =IF($B$4<$B$5,10,0)
B16) 0 =SUM(B14:B15)
Trend =IF($B$11=2,"Up",IF($B$16=20,"Down",""))
I know some of the steps can be eliminated by combining the formulas but then its too confusing to figure out what is going wrong.
=IF(OFFSET($B$2,0,(COUNT($B$2:$M$2)- COUNTIF($B$2:$M$2,0))-1)>OFFSET($B$2,0,(COUNT($B$2:$M$2)- COUNTIF($B$2:$M$2,0))-2),1,0)
Thank you for your time.
