Calculations (formula) - help needed

Baserow Help
#1

Hi,
please, can anyone here help me to set up properly some calculation fields (formula) in my table?

Let’s say I have a table with student’s names where one of my columns (fields) is a number type, where I put manually the year, when student finished his exam (or let’s say he finished the high scool). I will name this field as “EXAM”.

I would like to create two more fields:
One field - I will call it “CALC”- auto-calculated, where number 25 is subtracted from the “EXAM” field and result is displayed here.
And then another field (I will call it “CALC2”), which will use actual year as an input number (so just now it would be a number 2023, but next year it will be number 2024) and then the number from “CALC” field will be substracted from it and will display the result number.

Example:
if I will put the number 2015 in the “EXAM” field, then in “CALC” field I should see the number 1990 and in “CALC2” field I should see the number 33.

One more thing: if the “EXAM” field is empty, then no calculations should be done.

Thanks!

#2

Hello @marcus, here you are: Grid // Baserow.

Field CALC: formula field('Exam') - 25
Field CALC2: formula year(now()) - field('CALC')

Hope it helps :slightly_smiling_face:

#3

Great, works. Thanks for it.
Just one more thing: how can I add the exception to CALC anc CALC2 fields? I mean, if EXAM field is empty, then also fields CALC and CALC2 should be empty too.

#4

Hey, you can achieve that by using functions if and is_null, here’s how it works: Grid // Baserow.

Field CALC: if(is_null(field('Exam')), '', field('Exam') - 25 )
Field CALC2: if(is_null(field('Exam')), '', year(now()) - field('CALC'))

#5

Hi Olga, wonderful, thanks for your help.

First formula works correctly, but second formula

if(is_null(field('Exam')), '', year(now()) - field('CALC'))

which is for field CALC2 - - gives me this error: Error with formula: argument number 2 given to operator - was of type text but the only usable type for this argument is number.
It looks like the problem is that field CALC is formula type, but formula function expects (requires) standard number filed type. It’s silly, but if it’s like that, then I need some workaround for it.
Please, any tips?

I see in your example, that you have two more fields there and it works somehow. Is there anything special that you’ve done? I would like to inspect those fields, but they are read-only shared (so not possible to go to Edit and see, what exact formulas you’ve used)

obrazek
obrazek998×285 20.2 KB

Fields “CALC 2” and “CALC2 2” is the perfect result, I would like to achieve. (Doesn’t matter if there are some more field needed to be present in the table as a workaround - I can hide them ofcourse).

#6

Ahhh, I see!!! I need to keep those first created fields with formulas too and then add two more with those new formulas with exception :bulb:

#7

Glad to hear you figured that out! :raised_hands: