Creating Spreadsheet Calculations

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...
 
F

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top