# Subtraction using a column with a formula

✭✭

For some reason I cannot use a subtraction formula when pulling the info from a column that has an existing formula. Is that a thing or am I doing something wrong?

I'm using the simple formula below

=[PCA In service required]@row - [In service hours completed]@row

• Employee

Hi @Tzippy

It's the quotes around your numbers that turns them into text. For example:

IF(CONTAINS("PCA", Discipline@row), "6"

Should be

IF(CONTAINS("PCA", Discipline@row), 6

Try adjusting that formula to be this:

=IF(AND([Most recent hire date]@row < DATE(YEAR(TODAY()), 4, 1)), IF(CONTAINS("PCA", Discipline@row), 6), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 4, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 10, 1)), IF(CONTAINS("PCA", Discipline@row), 3), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 10, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 12, 31)), IF(CONTAINS("PCA", Discipline@row), 0))))

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭
edited 09/08/22

Hello @Tzippy

I think this is likely because in the "PCA In Service Required" column, there is a function (the small fx symbol) below the column name.

This is a shot in the dark, but perhaps we can try something like this:

=VALUE([PCA In service required]@row) - [In service hours completed]@row

Hope this helps!

• ✭✭

The fx just means that there is a column formula I believe...

I tried your formula but got the invalid operation error message.

Any other ideas?

• Employee

Hi @Tzippy

I agree that it looks like your column formula is turning your number into a text value. You can see this because the number appears on the left side of the cell instead of the right.

The VALUE() function should have converted it back to being a number. Since that didn't work, can you post what formula you're using in the "PCA in service required" column? We may be able to adjust that formula to ensure the output is numerical (e.g. removing any quotes around it).

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭

The formula in the pca in service column is:

=IF(AND([Most recent hire date]@row < DATE(YEAR(TODAY()), 4, 1)), IF(CONTAINS("PCA", Discipline@row), "6"), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 4, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 10, 1)), IF(CONTAINS("PCA", Discipline@row), "3"), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 10, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 12, 31)), IF(CONTAINS("PCA", Discipline@row), "0"))))

• Employee

Hi @Tzippy

It's the quotes around your numbers that turns them into text. For example:

IF(CONTAINS("PCA", Discipline@row), "6"

Should be

IF(CONTAINS("PCA", Discipline@row), 6

Try adjusting that formula to be this:

=IF(AND([Most recent hire date]@row < DATE(YEAR(TODAY()), 4, 1)), IF(CONTAINS("PCA", Discipline@row), 6), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 4, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 10, 1)), IF(CONTAINS("PCA", Discipline@row), 3), IF(AND([Most recent hire date]@row >= DATE(YEAR(TODAY()), 10, 1), [Most recent hire date]@row < DATE(YEAR(TODAY()), 12, 31)), IF(CONTAINS("PCA", Discipline@row), 0))))

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭

Yes!! That worked!! I had no idea about this quotations and on which side of the cell the number is! Thanks for teaching me that and your patience for helping me with this!!

• Employee

No problem! I'm glad to hear it worked 🙂