Formula Help

  • Thread starter Thread starter Martin ©¿©¬
  • Start date Start date
M

Martin ©¿©¬

Hi
Using 2010
I have a spreadsheet with 276 rows (can be variable)
In columns K,L & N,  I have financial amounts some of which are zero
or blank.
What I would like to do is when K,L & N are all zero or blank in the
same row is to highlight this row in colour so that it is easily
recognised

Anyone help please
Thank you
 
hi Martin,

Am Mon, 13 May 2013 19:32:10 +0100 schrieb Martin ©¿©¬:
I have a spreadsheet with 276 rows (can be variable)
In columns K,L & N,  I have financial amounts some of which are zero
or blank.
What I would like to do is when K,L & N are all zero or blank in the
same row is to highlight this row in colour so that it is easily
recognised
Click to expand...

select your table => conditional Formatting => Formula:
=SUM($K1,$L1,$N1)=0


Regards
Claus Busch
 
hi Martin,

Am Mon, 13 May 2013 19:32:10 +0100 schrieb Martin ©¿©¬:


select your table => conditional Formatting => Formula:
=SUM($K1,$L1,$N1)=0


Regards
Click to expand...

Thanks Claus,
When I click conditional formatting there is no 'formula' option?
 
Hi Martin,

Am Tue, 14 May 2013 16:56:56 +0100 schrieb Martin ©¿©¬:
When I click conditional formatting there is no 'formula' option?
Click to expand...

Conditional Formatting => New Rule => Use a *formula* to determine which
cells to format


Regards
Claus Busch
 
Hi Martin,

Am Tue, 14 May 2013 16:56:56 +0100 schrieb Martin ©¿©¬:


Conditional Formatting => New Rule => Use a *formula* to determine which
cells to format


Regards
Click to expand...

Claus, formula hits some lines , misses others & highlights some lines
with numbers in them
I checked that the column names are K, L & N against the formula
=SUM($K1,$L1,$N1)=0 - so I'm really puzzled as to what's wrong

My spreadsheet has columns from left to right
A, B E, K, L & N. as I removed some unnecessary columns
Perhaps that's what's messing things up? (only guessing)
 
Hi Martin,

Am Wed, 15 May 2013 15:17:26 +0100 schrieb Martin ©¿©¬:
Claus, formula hits some lines , misses others & highlights some lines
with numbers in them
I checked that the column names are K, L & N against the formula
=SUM($K1,$L1,$N1)=0 - so I'm really puzzled as to what's wrong
Click to expand...

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
for the file "Martin" and right-click and download it.


Regards
Claus Busch
 
Hi Martin,

Am Wed, 15 May 2013 15:56:48 +0100 schrieb Martin ©¿©¬:
Then what do I do?
Click to expand...

compare your conditional formatting with mine and find the error.
Or paste your data in my workbook and look if the conditional formatting
is correct.


Regards
Claus Busch
 
On a related note, can the "financial amounts" be sometimes negative?

If so, then the sum might be zero by combining a positive with a negative.

So perhaps consider a conditional formula like:
=AND(K1=0,L1=0,N1=0)
 
On a related note, can the "financial amounts" be sometimes negative?

If so, then the sum might be zero by combining a positive with a negative.

So perhaps consider a conditional formula like:
=AND(K1=0,L1=0,N1=0)
Click to expand...

Thanks for your reply
There are no zeros anywhere in the spreadsheet, I looking for a line
of 3 Zeros in adjacent colums K, L & N
 
Back
Top