# Software

## Question

Locked

### How to make an Excel Offset formula recognize zeros?

By ·
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)

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

## Related Discussions

• 0

#### Component Used to to Convert an Old Computer to a NAS Storage

supramsinfo ·

• 3

#### LMS Software

singhsima002 ·

• 1

odooncon ·

• 1

#### About Best Customer Relationship Management (CRM) Software.

priti1603775985 ·

• 1

jfearn91 ·