Problem with multiplier formula (not recognizing decimal values)

[Brian_Zayas]

Hello-

I have a formula field that multiples values from two other rows. It functions properly when multiplying values without decimal points, e.g., 1, 2, 3, 4, etc. But fails to account for values with decimal points, e.g., 1.5, 2.5, 3.5. The odd thing is that for those values (the ones with decimal points), it actually accounts for the numeric portion of the values without the decimal value. Specifically, for 1.5 or 2.5, it will multiply based on 1 or 2, respectively. It ignores that there is a .5 tacked on as well… Here is the formula:

IF({Mins}=15,".5",IF({Mins}=30,“1”,IF({Mins}=45,“1.5”,IF({Mins}=60,“2”,IF({Mins}=75,“2.5”,IF({Mins}=90,“3”,IF({Mins}=105,“3.5”,IF({Mins}=120,“4”," "))))))))

Not sure if this is helpful, but when I click on the “formatting” tab within the field, here is the response:

“Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.”

Thanks in advance!

[ScottWorld]

Welcome to the community, @Brian_Zayas!

A few things:

1. Don’t put your numbers inside of quotation marks.

2. Remove the final ," " part of your formula.

So, your revamped formula would look like this:

IF({Mins}=15,.5,IF({Mins}=30,1,IF({Mins}=45,1.5,IF({Mins}=60,2,IF({Mins}=75,2.5,IF({Mins}=90,3,IF({Mins}=105,3.5,IF({Mins}=120,4))))))))

However, you could really cleanup that formula by using a simple SWITCH statement instead:

Switch(
Mins,
15,.5,
30,1,
45,1.5,
60,2,
75,2.5,
90,3,
105,3.5,
120,4
)

Whichever formula you use, both of these formulas will always result in a NUMBER being returned to Airtable, and then you can do the 3rd thing:

3. Go into the formatting for your formula field, and change it from Integer to Decimal.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

[Brian_Zayas]

ScottWorld:

IF({Mins}=15,.5,IF({Mins}=30,1,IF({Mins}=45,1.5,IF({Mins}=60,2,IF({Mins}=75,2.5,IF({Mins}=90,3,IF({Mins}=105,3.5,IF({Mins}=120,4))))))))

Thank you Scott. I tried the first change (and after switching the number to a decimal) received the same result. With regard to the switch statement, once I enter the formula, click save, etc, the value in that field appears blank. And the field that uses the multiplier returns “NaN”. Does this make sense?

[ScottWorld]

Can you post a few screenshots of your table, along with a screenshot of your actual formula?

[Brian_Zayas]

Sure:

The step value column shows what the step equivalent is for 30 minutes of certain exercise activities.

The Mins column shows how long a participant actually spent with a given activity.

The multiplier is supposed to update the steps, based on time. Thus, if an activity is 30 minutes, it need only multiply the steps by 1. If the activity is 45 minutes, the multiplier is 1.5, to account for the additional 15 mins.

Does this make sense?

[ScottWorld]

Yes, it makes sense, and according to your screenshot, everything is working perfectly. What is the problem you’re trying to solve?

[Brian_Zayas]

The values aren’t multiplying correctly, at least not for the values that contain decimals. For example, in the screenshot: 2970 * 1 = 2970.0 (we’re good there). But 3630 * 1.5 does not equal 3630. Same problem with the 3180 * 3.5. The total is 9540 but should be 11,130.

[ScottWorld]

You haven’t shown the formula for “Total Steps”. I would need to see what that formula is, in order to help you with the “Total Steps” formula. So far, you’ve only been talking about the “Multiplier” formula.

Also, your “Step Value” is a lookup field, which might be part of the problem as well. But let’s see what your formula is for “Total Steps”.

[Brian_Zayas]

Ahh. Good point. It’s: INT({Step Value})*INT({Multiplier})

[ScottWorld · Accepted solution]

Okay, so you have 2 problems here:

1. Problem #1 is that the INT function means “Integer”, which removes all the decimal points from your numbers. It only takes the part of your number before the decimal point. So you’ll need to get rid of that.

2. However, problem #2 is that you’re multiplying a Lookup Field (“Step Value”). A Lookup Field, although it can be formatted to display as a number, is actually an array. So you’ll need to first convert the lookup field into a string, and then convert the string into a number after that. The way to do that is with this formula: VALUE({Your Lookup Field Goes Here} & ""). Note that this will only work if you have ONE NUMBER ONLY in your lookup field. If you have more than one number in your lookup field, you would need to take an entirely different approach to this entire problem.

So, your final formula for “Total Steps” would look like this:

VALUE({Step Value} & "") * Multiplier

3. Then, after you’ve created your formula, go into the “Formatting” tab for your formula, and change it from “integer” to “decimal”, and specify the number of decimal points that you want to see. If you specify too low of decimal points, then your number will be ROUNDED to the decimal point that you specify.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face: