Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
XP XP is offline
external usenet poster
 
Posts: 389
Default Formula that checks balances

I have a long list of formulas in column "E".

I need to know at a glance whether any value in this column is inequal to
zero without having to page down all the way through thousands of rows.

Summing the column is no good because there could be offsetting values (e.g.
+5 + -5 = 0).

I would like a formula I could place in one cell that would indicate if
every formula in the column evaluates to zero or not (rounded to 2 decimals).
Can anyone help?

Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 905
Default Formula that checks balances

"xp" wrote:
I would like a formula I could place in one cell
that would indicate if every formula in the column
evaluates to zero or not (rounded to 2 decimals).


First, it would be a good practice if you ensured that each formula rounded
to 2 decimal places, either by explicitly using the ROUND function
(preferred) or by using the "Precision As Displayed" calculation option in
conjunction with a number format other than General.

If you do that, then the following might meet your needs:

=COUNTIF(E1:E100,"<0")

Otherwise, try:

=SUMPRODUCT(--(ROUND(E1:E100,2)<0))

Note that these formulas return a count of non-zero values, not simply "if
every cell is zero". I think the count is more useful.

FYI, I usually do this inconjunction with a parallel column (e.g. F1:F100)
of the form:

=IF(ROUND(E1,2)=0,"","ERROR")

Then the first formula can be simply: =COUNTIF(F1:F100,"ERROR")


----- original message -----

"xp" wrote:
I have a long list of formulas in column "E".

I need to know at a glance whether any value in this column is inequal to
zero without having to page down all the way through thousands of rows.

Summing the column is no good because there could be offsetting values (e.g.
+5 + -5 = 0).

I would like a formula I could place in one cell that would indicate if
every formula in the column evaluates to zero or not (rounded to 2 decimals).
Can anyone help?

Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default Formula that checks balances

hi
use a countif formula
=countif(E2:E10,0)
the above would tell you if you have any formulas that equate to zero.
to find where that is use the following....
=ADDRESS(MATCH(0,E2:E10,0),1,1)
the above will only show the first instance of a zero value. in case you
have more than one.

regards
FSt1


"xp" wrote:

I have a long list of formulas in column "E".

I need to know at a glance whether any value in this column is inequal to
zero without having to page down all the way through thousands of rows.

Summing the column is no good because there could be offsetting values (e.g.
+5 + -5 = 0).

I would like a formula I could place in one cell that would indicate if
every formula in the column evaluates to zero or not (rounded to 2 decimals).
Can anyone help?

Thanks!

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 91
Default Formula that checks balances

On May 4, 1:15*am, FSt1 wrote:
hi
use a countif formula
=countif(E2:E10,0)
the above would tell you if you have any formulas that equate to zero.
to find where that is use the following....
=ADDRESS(MATCH(0,E2:E10,0),1,1)
the above will only show the first instance of a zero value. in case you
have more than one.

regards
FSt1



"xp" wrote:
I have a long list of formulas in column "E".


I need to know at a glance whether any value in this column is inequal to
zero without having to page down all the way through thousands of rows.


Summing the column is no good because there could be offsetting values (e.g.
+5 + -5 = 0).


I would like a formula I could place in one cell that would indicate if
every formula in the column evaluates to zero or not (rounded to 2 decimals).
Can anyone help?


Thanks!- Hide quoted text -


- Show quoted text -


One another
=SUM(E1:E100*E1:E100)
Just press Ctrl+Shift+Enter in place of Enter as this is an array
formula.
If any non zero value is there then the formula result will be 0

Or if you do not use formula one other way.

Select the E1:E100----If E1 is "0" then press Ctrl+Shift+\ ----
This will select all cells not "0"
In case the E1 is not zero you can make it 0 for time being.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
need help with creating a formula which checks more than 40 cells Faizan Excel Worksheet Functions 1 January 3rd 10 10:13 AM
Formula that checks several criteria before returing the value??? Apinun Excel Discussion (Misc queries) 4 September 19th 06 10:17 AM
Formula/function that checks for a set of values. Memnok Excel Worksheet Functions 8 March 24th 06 06:59 PM
formula to check balances within parameters Todd Excel Worksheet Functions 2 March 9th 06 01:00 AM
How to link endings balances to beginning balances on different sh judyskiskd Excel Worksheet Functions 1 October 28th 04 07:21 PM


All times are GMT +1. The time now is 07:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"