Sep 24, 2020 09:05 AM
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!
Solved! Go to Solution.
Sep 24, 2020 12:41 PM
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
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:
Sep 24, 2020 09:35 AM
Welcome to the community, @Brian_Zayas!
A few things:
Don’t put your numbers inside of quotation marks.
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:
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:
Sep 24, 2020 09:53 AM
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?
Sep 24, 2020 09:59 AM
Can you post a few screenshots of your table, along with a screenshot of your actual formula?
Sep 24, 2020 10:21 AM
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?
Sep 24, 2020 11:42 AM
Yes, it makes sense, and according to your screenshot, everything is working perfectly. What is the problem you’re trying to solve?
Sep 24, 2020 11:45 AM
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.
Sep 24, 2020 11:54 AM
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”.
Sep 24, 2020 12:09 PM
Ahh. Good point. It’s: INT({Step Value})*INT({Multiplier})
Sep 24, 2020 12:41 PM
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
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:
Sep 24, 2020 04:06 PM
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).
Sep 24, 2020 04:08 PM
I think you should be fine.
Sep 24, 2020 04:10 PM
Scott- Thank you so much for your guidance. This platform is cool, but the community and available support is even better. Very much appreciated.
Sep 24, 2020 04:16 PM
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:
Sep 24, 2020 04:51 PM
I appreciate the nod, Scott, but I can guarantee you that I’m not good enough to be listed twice. :winking_face:
Sep 24, 2020 04:53 PM
OMG, hahaha!!! :joy: :rofl: :grinning_face_with_sweat:
I actually meant to tag @Jeremy_Oglesby!! Too many names starting with J!! Lol.
Sep 24, 2020 05:09 PM
Thanks again all. :thumbs_up: