Friday, November 21, 2014

yogi_Calculate Values Based On Data in Another Sheet

               Google Spreadsheet   Post  #1842
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-21-2014
post by LoicLoic:
(https://productforums.google.com/forum/#!mydiscussions/docs/uIzkBl_XMZc)
calculate cell value after it without circular reference issue
Consider the following

In cells A1, A2, A3, Ax I have 1, 2, 3 respectively. In cells B1, B2, B3, Bx I will introduce numeric values.

I need those value to automatically divide by A1, A2, A3. For example, once I put 100 in B2, it should show 50 instead (B2/A2=50).


My original idea is to input this data on one sheet, and then use arrayformula to call it into another sheet where I can have the calculation actually take place. But I'm not sure how to alter the arrayformula.

Thoughts?
---
I get a circular reference when I use that formula.


Input sheet is where data is added
output sheet is the expected result (colored to show what's being divided)
----------------------------------------------------------------------------------------------------------------------------------------