Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas for changing formulas?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas for changing formulas?
Hi creativeops,
does: EditReplace...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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas for changing formulas?
Yeah, that's it - thanks Ken. Duh, I don't know why I didn't realize that.
"Ken Johnson" wrote: Hi creativeops, does: EditReplace...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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas for changing formulas?
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ken Johnson" wrote in message oups.com... Hi creativeops, does: EditReplace...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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formulas for changing formulas?
oh wow, that's a great idea David, thanks!
"David McRitchie" wrote: 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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ken Johnson" wrote in message oups.com... Hi creativeops, does: EditReplace...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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
Countif formulas change after doing a sort | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |