Calculations based on cell selection

[Shaun]

I am using Excel 2002 and I was wondering if there is a way to perform
a calculation on a cell selection area. More specifically, I would
like to have a cell at the top of my worksheet that contains a formula
(=SUM(x)*.04) where "x" is a range of cells I have selected, i.e.,
highlighted. I know the sum of the selection can be displayed in the
status bar at the bottom of the page, but I would like to have that
value multiplied by .04 displayed in a cell in the worksheet. Of
course this value would also have to be auto-calculated as the
selection changes. It could be displayed in the status bar though if
that is easier, it is not vital that it has to be in a cell.

My initial thinking is that this would have to be done with some VBA,
but I do not know VBA well enough to know where to begin doing it.

Any relevant help or guidance welcomed.

Thanks,

Shaun

 

[Dave R.]

Since you're going to highlight, might as well go to Insert>Name>Define, and
call the range whatever you want (e.g. apple). Then use

=SUM(apple)*.04

 

[Frank Kabel]

Hi
though i wouldn't do it you may use the following event procedure (put
it in your worksheet module). It will print your value in cell B1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error GoTo error_check
Me.Range("B1").Value = Application.WorksheetFunction.sum(Target) *
0.4

error_check:
Application.EnableEvents = True
End Sub

 

[Shaun]

Thank you so much Frank. This is exactly what I was looking for! It works perfectly!

But out of curiosity, you say you would not do it. Why is that?

--Shaun