# Create formulas without actual values

#### mrtobsy

##### New Member
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
ABCD
4EstimatedResalePrice
5PurchasePrice
6PurchaseClosingCosts
7EstimatedCostsofImprovements
8CarryingCosts
9MarginofSafety
10ResaleClosingCosts&Commissions
11Subtotal
12CapitalGainsTax
13NetProfit
Sheet1

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Richard Schollar

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

Replies
3
Views
147
Replies
5
Views
435
Replies
2
Views
141
Replies
3
Views
311
Replies
5
Views
182

1,141,678
Messages
5,707,780
Members
421,527
Latest member
Tamiwsw

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back