Need help for Custom Column Formula

[confusedstudent]

I've got a column with mixture of figures (eg 2,000 and 2,000 - 5,000) and i'm suppose to calculate the average of that range (eg [2,000 + 5,000]/2 = 3,500). 

I've tried using delimiter to spilt the range and used custom column to calculate the average range. But my original solid figure of 2,000 doesn't appear in the new column column and it appeared as null?  

Is my formula for the custom column missing something? 

=([Figure.1]+[Figure.2])/2

[az38]

Hi @confusedstudent 

how is your data look like in data source?

and [2,000 + 5,000]/2 is equal 3500, not 4500, I think 🙂

[confusedstudent]

[az38]

@confusedstudent 

so, whats your business logic?

how do you want to calculate Figure_tidy if Figure2 is null?

[az38 · Accepted solution]

@confusedstudent 

try create a custom column

if [Figure 2] = null then [Figure 1] else ([Figure 1] + [Figure 2])/2

[confusedstudent]

It works! Thank you! 

[confusedstudent]

I need to pop the 300,000 and 500,000 over to the Figure_Tidy Column. How could i do it?