Question

Locked

How to make an Excel Offset formula recognize zeros?

By dcdfntech ·
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.

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Back to Software Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums