smashclash
Board Regular
- Joined
- Nov 24, 2003
- Messages
- 126
- Office Version
-
- 365
- Platform
-
- Windows
I create a monthly report that usually has 6 columns of data. They are as follows: Month Actual, Month Budget, Month Variance, Year Actual, Year Budget, Year Variance. My goal is to eliminate any row who has a 0 balances across. Currently I reference the cells to the right in other columns and take their absolute values. Then I sum those lines. Any sum with a value of 0 can consequently be deleted.
The reason I have to use absolute values to sum a row instead of just taking the sum is as follows. Say a particular row has no month activity, so all 3 columns related to the month are 0. But the year actual has a balance of $100 and the year budget has a balance of 0. Well the year variance would be -$100 and a sum of the line would net a 0 balance. Therefore if I take the absolute values I ensure myself that I will not delete any rows that contain activity.
If anyone knows a function or how to right this into a macro I would be much appreciative. I think I currently have the problem solved by using Access with setting up each "Or:" criteria as <>0. However I would like to know a way to do this in excel. Thanks.
The reason I have to use absolute values to sum a row instead of just taking the sum is as follows. Say a particular row has no month activity, so all 3 columns related to the month are 0. But the year actual has a balance of $100 and the year budget has a balance of 0. Well the year variance would be -$100 and a sum of the line would net a 0 balance. Therefore if I take the absolute values I ensure myself that I will not delete any rows that contain activity.
If anyone knows a function or how to right this into a macro I would be much appreciative. I think I currently have the problem solved by using Access with setting up each "Or:" criteria as <>0. However I would like to know a way to do this in excel. Thanks.
NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.
