Simplifying Edits of Complex Formulas for a 24-hour table? 2

[racookpe1978]

Request your guidance in this: I am a long-time "basic" user of spreadsheets since Windows 3.1 began, but am at best only a "amateur" and certainly am not a programmer

Assume I have a multi-sheet Excel 2007 spreadsheet.
I know can declare several "Variables" in specific cells on that sheet specifically, or for all of the sheets in the file:
For example: B1 = LAT (latitude), B2 = DOY (day-of-year), B3 = HRA (hour angle),
From those named variables, in the past, I have simply typed very long-winded, very complex formulas into an array of cells, and then copied every formula "down" to the 24 rows underneath to repeat the equation into lower rows (Ctrl+D) using either row references (cell B10 = cell A10/2 for example) or a mix of row reference cells and fixed reference cells (B10 =cell A10/2 + cell $d$4 for example).

But how can I write my cells in a 24-hour table so I do not have to duplicate all of the formulas into every cell?

I would prefer to edit and test these very complex formulas for the many cells once, not endlessly use ctrl-d for copying the formula?

2. to support the final table, I will need to edit equations for declination angle (a function of DOY), solar elevation angle SEA (a function of DOY, LAT, HRA), albedo of water (A function of SEA), albedo of ice (a function of DOY), Air_Temp (a function of DOY and LAT and HRA again), Direct-to-Diffuse ratio (also a function of SEA), etc.

Is there a simple solution, or is the most productive way to just copying the formulas down from cell-to-cell down as i have always done in the past?

So, if in cell C3 i have a long formula that uses named variables and the results from cells A6, B6, and C6, how do I write the results of that formula into cell D6?

When I want to use that same formula in C3 for the next row down (D7), how do I call the results for the row down: How do cells A7, B7, C7 get into the formula in C3 to populate cell D7?

 

[MintJulep]

Learn how to use Structured Tables.

http://msdn.microsoft.com/en-us/library/office/bb833052(v=office.11).aspx

 

[IDS]

Also consider using VBA.

If you are frequently using very complex formulas, replacing them with your own User Defined Functions has the potential to save a great deal of time, and also reduce the potential for errors.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[racookpe1978]

Thank you Mint - I've bookmarked that page, but it (and others) covers filling in rows in spreadsheets, not linking formulas in one place to cells in another place "down" rows to process values from other columns. I'll keep trying it.

IDS: Kind of hate to try to learn VBA at my advanced age ... but User Defined Function?

So, if my spreadsheet variables are DOY (day_of_year), LAT (latitude) and Hour, and I keep everything in Excel's radians ...
then
TAU (Day_Angle) => =2*3.1415*(DOY-1)/365
(TAU depends on only one variable, so no problem)
...but...
DECL (declination angle of the sun) => =0.006918-0.399912*COS(TAU)+0.070257*SIN(TAU)-0.006758*COS(2*(TAU))+0.000907*SIN(2*(TAU))-0.002697*COS(3*(TAU))+0.00148*SIN(3*(TAU))
and
SEA (Solar elevation angle) =>
=ASIN( (SIN(DECL)*SIN(LAT)) - (COS(DECL)*COS(LAT)*COS(HRA))
both will change based on the "row" variable HOUR and its HRA value.
so, since many other columns will depend on SEA(HRA) for example,

how would I convert the "named variable" format of
=ASIN( (SIN(DECL)*SIN(LAT)) - (COS(DECL)*COS(LAT)*COS(HRA))
into the "rowed equation" in cell d10
=ASIN( (SIN(c10)*SIN(LAT)) - (COS(c10)*COS(LAT)*COS(b10))



(All of the above assumes my typing is correct, of course; but let's not worry about the actual math right now.) 8<_)

 

[IDS]

User Defined Functions are written in VBA, but they are really easy, as long as you keep it simple.

I have attached a spreadsheet with functions that should work. Press F11 to look at the code, and enter the functions on the spreadsheet just like any other Excel Function.

Some notes:
I have started with "Option Explicit" because this is a good idea when things get more complicated, and it makes the code run faster, but it means you have to declare any variables before you use them, e.g.:
Dim T As Double
but:
Dim T
would also work.

Variables included as a function argument don't need to be declared again (in fact they can't be). Again, the "as Double" part is optional.

The actual functions were copied and pasted, except that I declared Pi as a constant, and Asin has to be preceded by "WorksheetFunction." because VBA does not have an ASin function.

I have used HRA as an input argument, but it would be easy to write a function for that. If it is from a lookup table on the spreadsheet it would be something like:

Function HRA(HRARange as range, Hour as double) as double
HRA = WorksheetFunction.VLookup(Hour,HRARange,2)
End Function

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

You might find this link useful as well:

http://newtonexcelbach.wordpress.com/2008/02/21/hello-world-in-a-udf/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[racookpe1978]

Will do. Am working through these.

(A tip to both above! )