Category subtotalling

[Guest]

I am trying to minimize the amount of space this spreadsheet takes up. I
have Three colums. The first column is the category type (39 or 41). The
second column is the estimated cost before construction. The third column is
the actual cost. What I want to do is average the difference between
estimated and acutal cost for each category. There is no pattern to the
category selection. It depends on the job. I was trying to write an if,
then, statement but it has been awhile and I am not sure if you can do it
anyway. It was something like this...

average(IF(column1=39,(column2-column3)/column2),else if(column1=41,skip to
next row))

If you can make sense of all this let me know.

 

[Jim Rech]

Say the data runs from A1 to C12. This formula may do what you want (for
39). It is an "array formula". That means it has to be entered via
Ctrl-Shift-Enter rather than just Enter.

=SUM((C1:C12-B1:B12)*(A1:A12=39))/COUNTIF(A1:A12,39)

--
Jim
|I am trying to minimize the amount of space this spreadsheet takes up. I
| have Three colums. The first column is the category type (39 or 41). The
| second column is the estimated cost before construction. The third column
is
| the actual cost. What I want to do is average the difference between
| estimated and acutal cost for each category. There is no pattern to the
| category selection. It depends on the job. I was trying to write an if,
| then, statement but it has been awhile and I am not sure if you can do it
| anyway. It was something like this...
|
| average(IF(column1=39,(column2-column3)/column2),else if(column1=41,skip
to
| next row))
|
| If you can make sense of all this let me know.

 

[Guest]

I guess not. I don't understand the Ctrl-Shift-Enter part I think. If I put
in this equation I get "#value". If I take out the column references so that
it only uses the one row it works but that does not do it for me.

 

[Jim Rech]

I don't understand the Ctrl-Shift-Enter part I think

Ok. Copy my formula. Select a cell for it and press F2. Then press Ctrl-v
to paste it. But don't press Enter. Instead hold down Ctrl and Shift and
then press Enter.

If you don't like array formulas then you have to do this using multiple
cells, probably as you are now.

--
Jim
|I guess not. I don't understand the Ctrl-Shift-Enter part I think. If I
put
| in this equation I get "#value". If I take out the column references so
that
| it only uses the one row it works but that does not do it for me.
|
| "Jim Rech" wrote:
|
| > Say the data runs from A1 to C12. This formula may do what you want
(for
| > 39). It is an "array formula". That means it has to be entered via
| > Ctrl-Shift-Enter rather than just Enter.
| >
| > =SUM((C1:C12-B1:B12)*(A1:A12=39))/COUNTIF(A1:A12,39)
| >
| > --
| > Jim
| > | > |I am trying to minimize the amount of space this spreadsheet takes up.
I
| > | have Three colums. The first column is the category type (39 or 41).
The
| > | second column is the estimated cost before construction. The third
column
| > is
| > | the actual cost. What I want to do is average the difference between
| > | estimated and acutal cost for each category. There is no pattern to
the
| > | category selection. It depends on the job. I was trying to write an
if,
| > | then, statement but it has been awhile and I am not sure if you can do
it
| > | anyway. It was something like this...
| > |
| > | average(IF(column1=39,(column2-column3)/column2),else
if(column1=41,skip
| > to
| > | next row))
| > |
| > | If you can make sense of all this let me know.
| >
| >
| >