Create formulas without actual values

mrtobsy

New Member
Joined
Oct 16, 2006
Messages
4
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Oct 16, 2006
Messages
4
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
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top