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

Ann Foley · 02/28/20

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. · 03/02/20

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. · 03/02/20

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 · 03/04/20

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

Genevieve P. · 03/08/20

Any time!!

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

Best Answer

Answers

Help Article Resources

Categories