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 ARRAYFORMULA
s 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 ARRAYFORMULA
s 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 ARRAYFORMULA
s?
Best Answer
Since Calculated field needs defined range for Hours use:
For 3rd Calculated field - Count/Hour: