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
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