Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


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

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

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

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?

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

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

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

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 ...
TAU (Day_Angle) => =2*3.1415*(DOY-1)/365
(TAU depends on only one variable, so no problem)
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))
SEA (Solar elevation angle) =>
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
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<_)

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

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

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

Will do. Am working through these.

(A tip to both above! )

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close