Calculating with blanks in excel

I wanted to calculate total savings and percentages with blanks, for example if column B is blank then I the calculation should be blank as well in column C, this applies to column D for percentage difference as well..  See attachment.
C--Users-lfreund-Downloads-COST.xlsx
LUIS FREUNDAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Luis,

Maybe you need...

In C2
=IF(OR(A2="",B2=""),"",A2-B2)

Open in new window

and copy it down.

In D2 (for percentage change between Old and New Cost)
=IF(C2="","",B2/A2-1)

Open in new window

and copy it down.
C--Users-lfreund-Downloads-COST.xlsx
0
 
yo_beeDirector of Information TechnologyCommented:
Is this what you are looking for?  

capture.png
Here is the excel file.

C--Users-lfreund-Downloads-COST.xlsx
0
 
AlanConsultantCommented:
Hi LUIS,

Please see attached.

Does that do what you want?

I have entered a calculation in the percentage column, but you might be defining that percentage differently, so please check before using.

Thanks,

Alan.
EE-29089427-C--Users-lfreund-Downlo.xlsx
1
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
yo_beeDirector of Information TechnologyCommented:
I made a mistake in my formula

should have been after seeing Alan's xlsx file
=iferror(c2/a2,0)

Open in new window


Either one (Iferror or If) of the function get you the desired results.
0
 
yo_beeDirector of Information TechnologyCommented:
@subodh

What is the -1 for?  This will skew the value off by 1.  

Column with Number value - Blank  or Blank - Number value will just give the result a + or -.  From my POV the =IF(OR(A2="",B2=""),"",A2-B2) makes the formula much more complicated.  
I think that the suggestion that I made is the cleanest and addresses whether A# or B# are blank without any worry if I missed something.  I know my screenshot has an incorrect formula, but I did acknowledge it after seeing Alan's spreadsheet.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's the formula used for calculating the percentage change.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In other words, B2/A2-1 is equivalent to (B2-A2)/A2.
0
 
yo_beeDirector of Information TechnologyCommented:
Ok.
0
 
LUIS FREUNDAuthor Commented:
AWESOME!  Thanks guys!
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.