How to find difference between 2 rows, but only if data exists in both rows?
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!
Best Answer
-
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
Answers
-
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
-
Amazing! As always, thank you for your help, Genevieve!
-
Any time!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.6K Ideas & Feature Requests
- 56 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!