# 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.

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

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

## Related Discussions

• 10

345011 //

• 1

#### How to choose the best ETL tool?

davidharperint //

• 2

#### What CRM software do you use?

ninapavljukpipa //

• 0

#### HOW DO I LEARN MACHINE LEARNING?

Shubhangi_123 //

• 0