formulas for changing formulas?

[Guest]

Hello again my saviors,
I have a fairly sprawling worksheet that has a large variety of items as the
rows, and then the columns are months, so each item is broken down by month
in terms of volume sold. There are also columns after each 3 months for the
quarters which sum the numbers for each month, and a YTD one at the end that
sums the quarters. Also, the rows of items are grouped by type, so after
each grouping there are rows that subtotal that type, and then at the end
total the subtotals. So there are raw data cells which contain raw numbers
pulled from elsewhere, and interwoven are rows & columns of simple sum
formulas.
Question (finally):
I've copied this whole table onto a new worksheet, but I want to change all
the sum formulas to average formulas. But not all the sum formulas are
adding the same amount of items (i.e. one type may have 3 items & another 6,
YTD sums 4 quarters whereas quarters sum 3 months). Is there a formula to
change the additions to averages? One that would determine the number of
'things' that particular function is adding so it would know the right
divisor to use?

Thanks in advance!

 

[Ken Johnson]

Hi creativeops,
does:
Edit>Replace...SUM in the Find what: box and AVERAGE in the Replace
with: box > Click on Replace All button

do what you want?
I only tried it out on one cell with =SUM(E1:E20) and it worked OK.
Ken Johnson

 

[Guest]

Yeah, that's it - thanks Ken. Duh, I don't know why I didn't realize that.

 

[David McRitchie]

Hi creativeops and Ken,

Suggestion:
assuming that your AVERAGE formula would be on row 21

instead of
E21: =AVERAGE(E2:E20)

suggest using
E21: =AVERAGE(E2:OFFSET(E21,-1,0))

The offset indicates 1 row before, no chnages to column

The reason for the writing formula this way is so that you can
insert a row immediately above without having to change your
AVERAGE formula. For more information see
http://www.mvps.org/dmcritchie/excel/offset.htm

 

[Guest]

oh wow, that's a great idea David, thanks!

Hi creativeops and Ken,

Suggestion:
assuming that your AVERAGE formula would be on row 21

instead of
E21: =AVERAGE(E2:E20)

suggest using
E21: =AVERAGE(E2:OFFSET(E21,-1,0))

The offset indicates 1 row before, no chnages to column

The reason for the writing formula this way is so that you can
insert a row immediately above without having to change your
AVERAGE formula. For more information see
http://www.mvps.org/dmcritchie/excel/offset.htm