# Referencing a calculated value in another calculated value

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?

• For 2nd Calculated field - Hours use =(End - Start) * 24, since it needs defined range. For 3rd Calculated field Count/Hour = Count / ((End - Start) * 24). Sep 28, 2020 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. Sep 28, 2020 at 12:36
• Does this answer your question? Is there a way to create a calculated field using results of another calculated field?
– LWC
Mar 16, 2021 at 13:57

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