determining non numeric cells when used in iterative calculations

[witchdoc]

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?

 

[briand2]

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

 

[witchdoc]

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.

 

[pmover]

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

 

[ProjEngKLS]

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