Compare how close values are

[SKETCHwade]

I have 3 columns, Next service, Last service and current hours.
These are all in hours format, an example would be next service 1750, last service 1500, current hours 1680.

I would like to make an expression to result in the number that was closest to current. In the example above it should return 1750.

Any ideas on how to do this?

[Aleksi]

1680? Hours format? Are these number fields?

[SKETCHwade]

Sorry, yes they are all number field

[Aleksi · Accepted solution]

Probably like IF(ABS(1750-1680)<ABS(1500-1680),1750,1500)

[SKETCHwade]

Thanks @Aleksi!

Would’nt happen to know why using the formula in initial value doesnt give a result but using it in formula works perfect? I would like it in initial in the off chance its incorrect.

[Aleksi]

It should work with the initial value as well. Though if you have checked it with the existing record when updating it, it would make sense. Initial value is calculated only when you add a new record… unless you use the option “Reset on edit?”

[SKETCHwade]

Yeaht the reset on edit was the solution, I’ve never seen or needed that function before. Thank you @Aleksi

[Aleksi]

You’re welcome