Directly input “duration” data type into Numbers formula

numbers

I am trying to create a formula in Numbers which takes a duration and calculates what percentage this is of another duration.

For example, I have a duration of 1h 30m in cell A1. I want to calculate what this is as a percentage of 9h. I am trying to output 17%.

I tried diving the cell A1 by 9, but this (rightfully) outputs a duration as A1 is being divided by a number. I need to divide A1 by another duration (i.e. 9h) in order to get a fraction. Is it possible to do this directly in the formula? The only other way I can how to do it is by inputing a duration of 9h into cell B2, and then writing =A1/B2. However, I would prefer being able to avoid using the auxiliary cell B2 when performing this calculation.

Best Answer

Divide by text in quotes.

So if you want to divide A1 which contain "1h 30m" by "9m" then use the following function A1 ÷ "9m" (A1 contains "1h 30m"). This is the equivalent of putting "9m" in B1 and then in using the function "A1 ÷ B1" in C3: Both will give you 10 because 9 minutes goes into 1 hour 3 min 10 times.

Related Solutions

Numbers: Grouping daily data into weekly data

Figured this out, finally. Thanks, community!

I created a table with weekly dates. Then I added summary columns for the daily data:

=SUMIFS(
  column_I_care_about_from_daily_spreadsheet,
  date_field_in_daily_spreadsheet, ">="&date_in_this_spreadsheet, 
  date_field_in_daily_spreadsheet, "<"&date_in_this_spreadsheet
)

I then added a "summary" column to match the weekly data

=SUMIF(
  date_field_in_daily_spreadsheet,
  "="&$date_in_this_spreadsheet,
  column_I_care_about_from_daily_spreadsheet
)

Two things: - Yes, the second formula is overkill - Yes, SUMIF and SUMIFS reverse their fields from each other

From here, I could easily get a graph.

How to handle Numbers’ automatic Data Formatting

Without access to the spreadsheet that you've shown, I've attempted to recreate this myself. It appears that there are two things wrong with the spreadsheet above:

The test-array for the countif is of Data Format Automatic. This means that the zeros are not of format Duration and can't be compared by the countif.

To fix this, select the entire array and choose Duration from the Data Format list.
The countif's condition is of format Number whereas the test-array is of format Duration.

To fix this, use the following for the countif cell:
```
=COUNTIF(G2:G11,">0h 0m")
```

Best Answer

Related Solutions

Numbers: Grouping daily data into weekly data

How to handle Numbers’ automatic Data Formatting

Related Question