Creating Spreadsheet Calculations

[Guest]

Hello,

I know that I can add a series of columns or calculate the value of a row's
cell by
using simple formulas such as =SUM(A1:A50) or =(A1*A3).

I also know that expandable and collapsable categories can be used in Excel.

My question is the following. If I have the following spreadsheet and I
find myself continuing to add new rows of data within a category, I then need
to keep changing my category formula to include the extra row. Can this be
setup to be done automatically ?

A B
1 Category 1 $100
2 lights $60
3 camera $40
4 Category 2

Now I find myself needing to add another field as follows where cell B1 needs
to have its formula changed from =SUM(B2:B3) to =SUM(B2:B4)

A B
1 Category 1 $170
2 lights $60
3 camera $40
4 action $70
5 Category 2

Thanks in advance for any insight...

 

[Frank Kabel]

Hi
it would be easier if you use 3 columns:
1. Category
2. Item
3. Value

and then use SUMIF or a pivot table

 

[Guest]

Another possibility (although it may be impractical for your application) is
to add the new values by inserting rows into the middle of the category,
rather than at the end. This should have the effect of expanding the formula.

Art