Create formulas without actual values

[mrtobsy]

How do i input formulas w/o any values. For the table below, i'd like to create a formula for the subtotal ( SUM(B5:B10) ) and total (C11-C12) w/ no having actual values. How can i do this w/o having a #Value error?

Thanks

DueDiligence.xls
	A	B	C	D
4	EstimatedResalePrice
5	PurchasePrice
6	PurchaseClosingCosts
7	EstimatedCostsofImprovements
8	CarryingCosts
9	MarginofSafety
10	ResaleClosingCosts&Commissions
11	Subtotal
12	CapitalGainsTax
13	NetProfit
Sheet1

 

[Richard Schollar]

Why are you getting #VALUE? Is this because you have them in the range being summed? In which case would it not be best to review the formulas in these cells first (ie B5:B10).

You could, alternatively, use a formula along the lines of:

=IF(ISERROR(SUM(B5:B10)),"",SUM(B5:B10))

to suppress error reporting.

Best regards

Richard

 

[mrtobsy]

Thank you for your help RichardSchollar.
If i want to make a template out of this worksheet w/ the subtotal (=C4-SUM(B5:B10)) and total (=C11-C12)
w/o any data in C4 and B5 to B10 (i could just input "=0" in each cell from B5toB10 and C4, but want to keep those blank to input any values).

My goal is to have those formulas "locked" (not sure it's the right word) so that for any values that i entered in C4, B5 to B10, the subtotal would automatically updated.

Thank you

 

[Richard Schollar]

If you have no values in any of the ranges ie C4 and B5:B10, a formula such as:

=C4-SUM(B5:B10)

should return zero - are you seeing a result different from this?

Richard