Avatar of Natchiket
NatchiketFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

Rookie Excel formula question

Hi I would like to use conditional formatting so that that if the result of a caculation is positive zero or negative then background colour the cell appropriately.  The issue is, if the cells being used are blank then I want the result to be blank (rather than zero), so that no formatting applies.

This is what I have at the moment:
=IF(AND(ISBLANK(E6),ISBLANK(G6)),"",E6-G6)
To try and get a blank target cell when E6 and G6 are blank, this seems rather ineffiecent, is there a better way of doing it?

The same with summation
=SUMIF(E6:E17,"<>""") still results in a '0' in the target cell how can I make the result blank if the range E6:E17 is blank?


Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)
Avatar of Natchiket
Natchiket
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Seems I have to use something like this:
=IF(SUMPRODUCT(--(E6:E7<>""))=0,"",SUM(E6:E7))
Again, is there a better way ?
One way is this...

If you want the SUM formula to sum the range E6:E17 if no cell in this range is empty, you may try the following formula...
=IF(COUNTBLANK(E6:E17)=ROWS(E6:E17),"",SUM(E6:E17))

Open in new window


and then you can use the below formula to make a new rule for conditional formatting for the formula cell.
e.g. if you formula cell is F6, the conditional formatting formula for highlighting the cell if the sum returned in F6 is greater than 0 would be...
=AND(ISNUMBER(F6),F6>0)

Open in new window

Same way you can construct the formula for other conditional formatting rules.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Why not simply create a new rule with "No Format Set" and put at the top of your rules?

make sure you ticked the "Stop if True" option for this rule too.

User generated image
Avatar of Natchiket
Natchiket
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Currently I am having this problem
Cell M6 has the formula: =IF(AND(ISBLANK(G6),ISBLANK(I6)),"",G6-I6) so it is blank if G6 and I6 are blank
But cell BE6 has the formula =M6+W6+AG6+AQ6+BA6   but the "" in M6 causes the expression #VALUE to appear in BE6
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo