asked on

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

yo_bee

Is this what you are looking for?

Here is the excel file.

C--Users-lfreund-Downloads-COST.xlsx

SOLUTION

Alan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

yo_bee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yo_bee

@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.

Subodh Tiwari (Neeraj)

That's the formula used for calculating the percentage change.

Subodh Tiwari (Neeraj)

In other words, B2/A2-1 is equivalent to (B2-A2)/A2.

yo_bee

Ok.

LUIS FREUND

ASKER

AWESOME! Thanks guys!