Help with using formula to calculate difference between 2 properties?

[PJohns]

Hi,

I can see that HS has a great feature when creating new fields that can populate the content, based on a formula cell.

We would like to know the difference between 2 existing properties and show that in a new property. For Example:

Property 1 contains a number, say: 5
Property 2 contains a number, say: 7

So we would like Property 3 to automatically show: 2

ie, the difference between the 2, but only if property 2 is higher than peroperty 1..

Would the formula feature be able to do this? Anyone had any experience of doing this or something similar, any help would be appreciated. Can't get my head round the correct way to right such a formula!

Thanks in advance.

Paul

[himanshurauthan]

Hi @PJohns, 

In the formula builder, you can use the following equation to calculate the difference between two properties, only if Property 2 is higher than Property 1:
IF(Property2 > Property1, (Property2 - Property1, 0)

Best regards, 

Did this post resolve your question? If so, please help the community by marking it as the accepted solution.

[karstenkoehler]

@himanshurauthan if you add 0 as the last argument, that will incorrectly return a difference of 0 for all records where number 1 is not greater than number 2, creating the false impression that the two numbers are the same.

[himanshurauthan]

 @karstenkoehler 

• IF(Property2 > Property1, Property2 - Property1, NULL):If Property2 is greater than Property1, it calculates the difference (Property2 - Property1).
• If Property2 is not greater than Property1, it returns NULL and Negative, which will leave the field blank or empty instead of showing 0

According to the formula, only they will give the null and value, and if there are two numbers same they will give the value null.

[karstenkoehler]

---

@himanshurauthan wrote:
IF(Property2 > Property1, (Property2 - Property1, 0)

---

In your previous answer, you shared a formula that returned 0 as the third argument, which would return 0, not leaving the field empty.

Additionally, NULL is not an accepted input as a third argument:

---

@himanshurauthan wrote:

IF(Property2 > Property1, Property2 - Property1, NULL):If Property2 is greater than Property1, it calculates the difference (Property2 - Property1).

---

Before posting replies, please make sure that they're not misleading / incorrect – especially if a correct solution was provided before.

[himanshurauthan]

@karstenkoehler We’ve explored all possible scenarios regarding this issue. We’ve also explained why the number appears as negative—because Property 1 is greater than Property 2, and Property 2 is greater than Property 1. Without adding a statement for zero, the value will show as null. 

With zero in the formula, we will receive 0 if property 1 is greater, which we have mentioned in a previous response. 

[karstenkoehler · Accepted solution]

Hi @PJohns,

This can be achieved with an IF statement 🙂

Best regards!

[PJohns]

Hi,

Brilliant - just what I needed. Works a treat! 🙂

+ Just created a workflow off the back of this field so if the number is 'known' then we get an alert by email.

Great - thanks!