CODEWITHSUNDEEP
excelexcel-formula
Robert Percy
Robert Percy

Reputation: 1

Leave cell blank if no value or divide function

I currently have a division formula dividing data from another sheet:

='Data Sheet'!E14/'Data Sheet'!E20

What function should be used in order to leave the cell blank if there is no data in the reference cells or show the division result if both data values are present?

Thanks in Advance

Upvotes: 0

Views: 1152

Answers (2)

Dominique
Dominique

Reputation: 17565

Here's how to build up such a formula:

if no data is in the reference cells (in other words, if one of those cells is blank):

Pseudo-code : ISBLANK(Ref1) OR ISBLANK(Ref2)
Excel formula : =OR(ISBLANK(Ref1);ISBLANK(Ref2))

In that case, what do you show? I suppose you want to show zero:

=IF(OR(ISBLANK(Ref1);ISBLANK(Ref2));0;Ref1/Ref2)

Which becomes finally:

=IF(OR(ISBLANK('Data Sheet'!E14);ISBLANK('Data Sheet'!E20));0;'Data Sheet'!E14/'Data Sheet'!E20)

Upvotes: 1

JohnMacgillivray
JohnMacgillivray

Reputation: 96

This formula will do as you request:

  =IF(OR('Data Sheet'!E14="",'Data Sheet'!E20=""),"",'Data Sheet'!E14/'Data Sheet'!E20)

Alternatively, if you only wanted to avoid DIV errors (i.e. were only concerned about the denominator being blank), the following would do:

  =IFERROR('Data Sheet'!E14/'Data Sheet'!E20,"")

Upvotes: 0

Related Questions