Help

Re: Problem with multiplier formula (not recognizing decimal values)

Solved
Jump to Solution
1434 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Brian_Zayas
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions

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

  1. 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:

See Solution in Thread

16 Replies 16

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:

  1. 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:

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?

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

Brian_Zayas
5 - Automation Enthusiast
5 - Automation Enthusiast

Sure:

image

image

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?

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

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.

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

image

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

  1. 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:

Brian_Zayas
5 - Automation Enthusiast
5 - Automation Enthusiast

So, I removed the “INT” function, and I think that did it. All the formulas produce the right results. If that’s the case, am I good to go? If I don’t make the other change you noted, will there be latent consequences? (Also, I don’t expect to have more than one number in my lookup field).

I think you should be fine.

Brian_Zayas
5 - Automation Enthusiast
5 - Automation Enthusiast

Scott- Thank you so much for your guidance. This platform is cool, but the community and available support is even better. Very much appreciated.

You’re welcome! Glad I could help! :slightly_smiling_face:

I totally agree with you that this Airtable community is one of my favorite things about using Airtable!!

And we’ve got some awesome superstars here in this community — @kuovonne and @Justin_Barrett and @JonathanBowen and @Justin_Barrett and @Kamille_Parks are some of my favorite superstars who inspire me on a regular basis, but there are a whole bunch of other rockstars besides them.

I hope nobody gets mad at me for forgetting them. There’s SEVERAL of them! Lol. :winking_face:

I appreciate the nod, Scott, but I can guarantee you that I’m not good enough to be listed twice. :winking_face:

OMG, hahaha!!! :joy: :rofl: :grinning_face_with_sweat:

I actually meant to tag @Jeremy_Oglesby!! Too many names starting with J!! Lol.

Brian_Zayas
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks again all. :thumbs_up: