Calculated comments in Dashboard

[Jacob Holloway]

Good morning all,

I have a dashboard that I am finishing up and I would like to have a calculated comment that states how many consecutive months a trend has lasted i.e. Travel has been over budget for 7 month(s).

Example of data:
Columns B:Q
Row 1 has past 16 months going from oldest to newest.
Row 2 has travel expenses over the past 16 months.

I've tried looking around for similar but searches keep returning how to use the TREND() and FORECST() formulas.

I initially though of having a Row 3 with the formula =IF(C$2>B$2,1,IF(C$2<B$2,-1,0)) and then do a SUMIF =1 for over and =-1 for trending under and divide that by the count of 0's but it is not working out.

Any help would be greatly appreciated!

Thank you!

 

[Luke M]

Sounds like you'd need to compare budget with actual expenses. Assuming that Budget is in row 1, and actuals are in row 3, then in B4, put this formula:

=IF(B2<=B1,MIN(0,A3)-1,MAX(0,A3)+1)

Copy to the right to col Q. Use value of Q3 to determine trend. Negative number means that you've been under budget, positive means you've been over budget. Absolute value of the number is how many months trend has lasted.

 

[Jacob Holloway]

Sounds like you'd need to compare budget with actual expenses. Assuming that Budget is in row 1, and actuals are in row 3, then in B4, put this formula:

=IF(B2<=B1,MIN(0,A3)-1,MAX(0,A3)+1)

Copy to the right to col Q. Use value of Q3 to determine trend. Negative number means that you've been under budget, positive means you've been over budget. Absolute value of the number is how many months trend has lasted.

Thanks Luke M. Works Great!