Contact US

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!

*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

determining non numeric cells when used in iterative calculations

determining non numeric cells when used in iterative calculations

determining non numeric cells when used in iterative calculations

In a building cost estimating spreadsheet, I use my final total to calculate some of the cost components.  For example, my engineering and permit fees are both a function of my final cost.  I set up an iterative calculation so that it closes to my final cost.  This works fine until a cell goes out of bounds, causing the cell formula to return a #REF result.  I would like to prevent this from happening.  Does anyone know of a formula or technique that will help this problem?

RE: determining non numeric cells when used in iterative calculations


Could you please give us an example of the contents, including formulae, in various cells, so that we have some detail to consider?



RE: determining non numeric cells when used in iterative calculations

For example, cost for each line item is calculated in column G, and summed at the bottom (G148).  The calculation for engineering cost, for example, would be a percentage of the total cost.  The formula for this cell might be =0.07*G148.  Cell G148 would contain the formula =sum(g1:g147).  The problem results when someone enters an erroneous input, or moves a cell, resulting in an error in the sum.  

RE: determining non numeric cells when used in iterative calculations

erroneous data input can be "controlled" by using the "DATA - VALIDATION" feature within xl.  from the menu system, select "DATA" - "VALIDATION" and make your selection.  note that this applies to the cell currently selected.  experiment with this feature and apply as needed as it really minimizes calculation errors.  also note that should an input value not be within specified conditions, a message box will appear (if set up properly).  personally, i frequently use this feature to limit input values and/or ensure the input values are within a specified range.  a handy tool if workbook is being used by others.

clarification is needed:  is one of the cells in column G computing =0.07*G148, with G148 containing the summation of cells G1:G148?

moving cells?  do not quite understand why cells are being moved.  please clarify.

RE: determining non numeric cells when used in iterative calculations

You mention iterative calculation, and I think that pmover might be touching on your problem when asking for a clarification.  

Are you using a circular reference?

You can use a circular reference, but why bother?  Why not subtotal your line items (i.e. subtotal), calculate your other costs (i.e. engineering) and add together to make a grandtotal, gandtotal = subtotal + engineering.

Seems very straight-forward unless we're missing something.

good luck,


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! Already a Member? Login


Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

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