How to find difference between 2 rows, but only if data exists in both rows?

Ann Foley

Hi,

I'm trying to make a formula that says 2020 Experiential Audit minus 2019 Experiential Audit = value in Experiential Audit YOY change...but only if there is data in both 2019 and 2020. See my sheet below.

I think this is an IF formula but I dont know how to say If there is a number...

Thank you in advance!

Genevieve P.

Hi Ann,

You're absolutely correct, this would be an IF statement!

Try this:

=IF(AND(ISNUMBER([2019 Experiential Audit]@row), ISNUMBER([2020 Experiential Audit]@row)), [2020 Experiential Audit]@row - [2019 Experiential Audit]@row, "")

This will only minus the 2019 column from the 2020 column if there are numbers in both cells of that row. Otherwise, it will return a blank cell.

Here are some Help Center articles I used: IF function / AND function / ISNUMBER function / @row function

Let me know if you have any trouble with this.

Cheers,

Genevieve

Genevieve P.

Hi Ann,

You're absolutely correct, this would be an IF statement!

Try this:

=IF(AND(ISNUMBER([2019 Experiential Audit]@row), ISNUMBER([2020 Experiential Audit]@row)), [2020 Experiential Audit]@row - [2019 Experiential Audit]@row, "")

This will only minus the 2019 column from the 2020 column if there are numbers in both cells of that row. Otherwise, it will return a blank cell.

Here are some Help Center articles I used: IF function / AND function / ISNUMBER function / @row function

Let me know if you have any trouble with this.

Cheers,

Genevieve

Ann Foley

Amazing! As always, thank you for your help, Genevieve!

Genevieve P.

Any time!!