Problem with multiplier formula (not recognizing decimal values)
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:
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:
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.
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:
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.
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?
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?
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?
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?
Yes, it makes sense, and according to your screenshot, everything is working perfectly. What is the problem you’re trying to solve?
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?
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.
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”.
Ahh. Good point. It’s: INT({Step Value})*INT({Multiplier})
Ahh. Good point. It’s: INT({Step Value})*INT({Multiplier})
Okay, so you have 2 problems here:
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.
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
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.
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).
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.
Scott- Thank you so much for your guidance. This platform is cool, but the community and available support is even better. Very much appreciated.
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!
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:
You’re welcome! Glad I could help!
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:
I appreciate the nod, Scott, but I can guarantee you that I’m not good enough to be listed twice. :winking_face:
OMG, hahaha!!! :grinning_face_with_sweat:
I actually meant to tag @Jeremy_Oglesby!! Too many names starting with J!! Lol.
:grinning_face_with_sweat: