0

Here's the general structure of my spreadsheet:

Start End   Count Duration Hours Count/Hour
09:00 10:00 10    01:00    1.00  10

Duration   = End - Start
Hours      = TIMEVALUE(Duration) * 24
Count/Hour = Count / Hours

Currently I use ARRAYFORMULAs in order to calculate Duration, Hours and Count/Hour for every row of the spreadsheet. It actually works pretty well but it doesn't seem like a clean solution. So I want to replace these ARRAYFORMULAs with a pivot table with calculated values.

I tried it and found that while the dynamic table is capable of calculating Duration it gives me an error when I reference Duration in the formula for Hours. It says the range is unknown.

Is it not possible to reference one calculated value in another? Seems like an odd restriction to have. The rest of the spreadsheet doesn't have this problem.

Is this possible in Google Sheets or should I keep using ARRAYFORMULAs?

3
  • 1
    For 2nd Calculated field - Hours use =(End - Start) * 24, since it needs defined range. For 3rd Calculated field Count/Hour = Count / ((End - Start) * 24). – Oleg_S Sep 28 '20 at 12:07
  • @OlegS Thanks, I think that's going to work. Please convert the comment into an answer so that I can upvote it. Why aren't calculated values considered valid ranges though? They look just like their ARRAYFORMULA counterparts and they generate a value for every row. I actually have several per hour fields and was hoping to avoid writing the cryptic (End - Start) * 24 calculation directly in their formulas if possible. – Matheus Moreira Sep 28 '20 at 12:36
  • 1
1

Since Calculated field needs defined range for Hours use:

=(End - Start) * 24

For 3rd Calculated field - Count/Hour:

= Count / ((End - Start) * 24)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.