×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# determining non numeric cells when used in iterative calculations

## determining non numeric cells when used in iterative calculations

(OP)
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

witchdoc,

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

Thanks,

Brian

### RE: determining non numeric cells when used in iterative calculations

(OP)
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.
-pmover

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

ProjEngKLS

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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!