Creating Spreadsheet Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

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...
 
Hi
it would be easier if you use 3 columns:
1. Category
2. Item
3. Value

and then use SUMIF or a pivot table
 
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
 
Back
Top