Numbers formula issue
BONNETON
-
Anonymous user -
Anonymous user -
Hello,
Here's my problem: my teacher sent me an Excel file, so I opened it on my Mac, but there's an issue: some formulas are not converting. Let me explain: It's a VMA calculation table. The problem arises when converting a numerical value into a duration, but with a formula in it: My formula is: =(100*0.04167)%(19.5*1000). The numerical result displays correctly, which is 0.0002136923076923080, but it should display 0:18.5, which is impossible to show. If I type the direct calculation result into a neutral cell, there’s no conversion issue, but because of the formula, it's impossible to convert...
Thank you for helping me solve this problem as I have been working on it for several hours without finding a solution!
Configuration: Mac OS X (10.10.3) / Safari 8.0.6
Here's my problem: my teacher sent me an Excel file, so I opened it on my Mac, but there's an issue: some formulas are not converting. Let me explain: It's a VMA calculation table. The problem arises when converting a numerical value into a duration, but with a formula in it: My formula is: =(100*0.04167)%(19.5*1000). The numerical result displays correctly, which is 0.0002136923076923080, but it should display 0:18.5, which is impossible to show. If I type the direct calculation result into a neutral cell, there’s no conversion issue, but because of the formula, it's impossible to convert...
Thank you for helping me solve this problem as I have been working on it for several hours without finding a solution!
Configuration: Mac OS X (10.10.3) / Safari 8.0.6
4 réponses
Hello,
To obtain the value you indicated, which is 0.0002136923076923080, the formula should actually be:
=(100×0.04167)÷(19.5×1000)/100
and not
=(100x0.04167)%(19.5x1000)
Next, what is the unit of time you are looking for... what exactly do these starting values correspond to?
0:18.5 should represent what kind of duration value?
seconds + milliseconds? The formula to use or adapt may depend on the meanings of the starting values...
For example, if I type the formula in the cell:
=DURATION(100×0.04167)÷(19.5×1000)/100
I get the value 00:01:292, which is equivalent to 0mn 1s 292ms
but the presentation format may vary according to the format chosen for the cell.
After knowing exactly what the starting numbers represent, and the desired result, we can look for a formula that fits...
Best regards
To get an appropriate response, always indicate your exact system configuration
Have a nice day :-) - Francis
To obtain the value you indicated, which is 0.0002136923076923080, the formula should actually be:
=(100×0.04167)÷(19.5×1000)/100
and not
=(100x0.04167)%(19.5x1000)
Next, what is the unit of time you are looking for... what exactly do these starting values correspond to?
0:18.5 should represent what kind of duration value?
seconds + milliseconds? The formula to use or adapt may depend on the meanings of the starting values...
For example, if I type the formula in the cell:
=DURATION(100×0.04167)÷(19.5×1000)/100
I get the value 00:01:292, which is equivalent to 0mn 1s 292ms
but the presentation format may vary according to the format chosen for the cell.
After knowing exactly what the starting numbers represent, and the desired result, we can look for a formula that fits...
Best regards
To get an appropriate response, always indicate your exact system configuration
Have a nice day :-) - Francis
I don't know what the value 0.04167 corresponds to; it must be a coefficient from..., but never mind. If we consider that the formula is okay, indeed, Numbers does not allow directly formatting the cell as desired.
But there's a trick that allows it: you should insert a column in which you paste the result of the formula (shortcut Shift-cmd-V), and give this cell the format "Duration"; see this example:
It creates an additional column, but again, we can use a trick that would go through an intermediate table to keep the number of columns and have the result directly in the final table...
To get an appropriate answer, always indicate your exact system configuration.
Have a good day :-) - Francis
But there's a trick that allows it: you should insert a column in which you paste the result of the formula (shortcut Shift-cmd-V), and give this cell the format "Duration"; see this example:
It creates an additional column, but again, we can use a trick that would go through an intermediate table to keep the number of columns and have the result directly in the final table...
To get an appropriate answer, always indicate your exact system configuration.
Have a good day :-) - Francis
The problem is that the table changes based on the value of the VMA, which in this case is 15 but can be 12 or another value; the entire table changes, especially the speed. In this solution, an action to "flatten the results" would be needed so that we don't have to copy 195 cells... this applies to each student having a different VMA.
OK - in that case, we'll need to be clever in another way.
Apparently, this is a calculation based on 24h x 60 min x 60 s = 86400.
To the original formula, we will therefore add a multiplying factor of 86400 and set the result cell to a decimal format with 3 units for direct reading in seconds. Example:
--
To obtain an appropriate response, please always indicate your exact system configuration.
Have a nice day :-) - Francis
Apparently, this is a calculation based on 24h x 60 min x 60 s = 86400.
To the original formula, we will therefore add a multiplying factor of 86400 and set the result cell to a decimal format with 3 units for direct reading in seconds. Example:
--
To obtain an appropriate response, please always indicate your exact system configuration.
Have a nice day :-) - Francis
Hello,
Here we can manage by using the QUOTIENT and MOD formulas
1 additional column for minutes and 1 additional column for seconds. In the end, if needed, we can hide the column that represents the total in seconds.
Examples:
Notes:
- the number 0.04167 is actually the inverse value of 24, (1/24), so it is indeed a calculation over 24 hours
- the value, here as an example 19.5 is the maximum speed of 15 km/h times 130%, if we multiply this by 1000, it's to get this value in meters/h
- the value 100 represents "distance traveled for observation" in meters
- given this data, we could make another table, a bit more precise. For example, the value 18.465 or 18.5 seconds is a value following the arbitrary number 0.04167, but the exact value would be 18.4613538461538....
-
To get an appropriate response, always indicate your exact system configuration
Have a great day :-) - Francis
Here we can manage by using the QUOTIENT and MOD formulas
1 additional column for minutes and 1 additional column for seconds. In the end, if needed, we can hide the column that represents the total in seconds.
Examples:
Notes:
- the number 0.04167 is actually the inverse value of 24, (1/24), so it is indeed a calculation over 24 hours
- the value, here as an example 19.5 is the maximum speed of 15 km/h times 130%, if we multiply this by 1000, it's to get this value in meters/h
- the value 100 represents "distance traveled for observation" in meters
- given this data, we could make another table, a bit more precise. For example, the value 18.465 or 18.5 seconds is a value following the arbitrary number 0.04167, but the exact value would be 18.4613538461538....
-
To get an appropriate response, always indicate your exact system configuration
Have a great day :-) - Francis
As for the representation of the figures:
The 19.5 corresponds to the speed in km/h. The 0.04167 is a fixed value that does not change. The 100 is a distance in meters. The 1000 is also a fixed value.
Here’s a little picture to clarify:
https://drive.google.com/file/d/0B4Uo96Cmv16Ba3RUaXp5d2tqTlE/view?usp=sharing