| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averages for cells
i have a spreadsheet that calculates averages, but as i add more columns, it
always calculates the same columns: B F G H I J ITEM AVG # ## # # the formula that i am using is =(SUM(G3:O3)-MIN(G3:O3)-MAX(G3:O3))/7 (drops the lowest and highest and make an olympic average to use). when i insert another row of the most recent data on the left side (push old data to the right, but keep the information). i insert a column between f and g (new column is now g...). the formula changes and continues to use the same values and not the new values... formula changes to =sum(h3:o3)-min(h3:o3)-max(h3:o3))/7 how do i get it the formula to limit itself to just the original selection, even if i insert columns in the selection? thank you, jat |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
averages for cells
jatman wrote:
i have a spreadsheet that calculates averages, but as i add more columns, it always calculates the same columns: B F G H I J ITEM AVG # ## # # the formula that i am using is =(SUM(G3:O3)-MIN(G3:O3)-MAX(G3:O3))/7 (drops the lowest and highest and make an olympic average to use). when i insert another row of the most recent data on the left side (push old data to the right, but keep the information). i insert a column between f and g (new column is now g...). the formula changes and continues to use the same values and not the new values... formula changes to =sum(h3:o3)-min(h3:o3)-max(h3:o3))/7 how do i get it the formula to limit itself to just the original selection, even if i insert columns in the selection? thank you, jat =(SUM(INDIRECT("G3:O3"))-MIN(INDIRECT("G3:O3"))-MAX(INDIRECT("G3:O3")))/7 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
averages for cells
hi
you need to understand the differenct between relative reference and absolute reference. look that up in xl help i think in your situation you want absolute reference or reference that don't change. relative references change when you move them or move the formula. to change form relatitive to absolute references and dollar signs i.e. =(SUM($G$3:$O$3)-MIN($G$3:$O$3)-MAX($G$3:$O$3))/7 you can put absolute reference on column, row, both. xl help will explain all that. regards FSt1 "jatman" wrote: i have a spreadsheet that calculates averages, but as i add more columns, it always calculates the same columns: B F G H I J ITEM AVG # ## # # the formula that i am using is =(SUM(G3:O3)-MIN(G3:O3)-MAX(G3:O3))/7 (drops the lowest and highest and make an olympic average to use). when i insert another row of the most recent data on the left side (push old data to the right, but keep the information). i insert a column between f and g (new column is now g...). the formula changes and continues to use the same values and not the new values... formula changes to =sum(h3:o3)-min(h3:o3)-max(h3:o3))/7 how do i get it the formula to limit itself to just the original selection, even if i insert columns in the selection? thank you, jat |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
averages for cells
(SUM(OFFSET(F3,0,1,1,9))-MIN(OFFSET(F3,0,1,1,9))-MAX(OFFSET(F3,0,1,1,9)))/7
Third Option... Offset hi you need to understand the differenct between relative reference and absolute reference. look that up in xl help i think in your situation you want absolute reference or reference that don't change. relative references change when you move them or move the formula. to change form relatitive to absolute references and dollar signs i.e. =(SUM($G$3:$O$3)-MIN($G$3:$O$3)-MAX($G$3:$O$3))/7 you can put absolute reference on column, row, both. xl help will explain all that. regards FSt1 "jatman" wrote: i have a spreadsheet that calculates averages, but as i add more columns, it always calculates the same columns: B F G H I J ITEM AVG # ## # # the formula that i am using is =(SUM(G3:O3)-MIN(G3:O3)-MAX(G3:O3))/7 (drops the lowest and highest and make an olympic average to use). when i insert another row of the most recent data on the left side (push old data to the right, but keep the information). i insert a column between f and g (new column is now g...). the formula changes and continues to use the same values and not the new values... formula changes to =sum(h3:o3)-min(h3:o3)-max(h3:o3))/7 how do i get it the formula to limit itself to just the original selection, even if i insert columns in the selection? thank you, jat |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
averages for cells
Put your formula in A1 and then insert a column at G (per the OP's
instructions). Now what's in A1? FSt1 wrote: hi you need to understand the differenct between relative reference and absolute reference. look that up in xl help i think in your situation you want absolute reference or reference that don't change. relative references change when you move them or move the formula. to change form relatitive to absolute references and dollar signs i.e. =(SUM($G$3:$O$3)-MIN($G$3:$O$3)-MAX($G$3:$O$3))/7 you can put absolute reference on column, row, both. xl help will explain all that. regards FSt1 "jatman" wrote: i have a spreadsheet that calculates averages, but as i add more columns, it always calculates the same columns: B F G H I J ITEM AVG # ## # # the formula that i am using is =(SUM(G3:O3)-MIN(G3:O3)-MAX(G3:O3))/7 (drops the lowest and highest and make an olympic average to use). when i insert another row of the most recent data on the left side (push old data to the right, but keep the information). i insert a column between f and g (new column is now g...). the formula changes and continues to use the same values and not the new values... formula changes to =sum(h3:o3)-min(h3:o3)-max(h3:o3))/7 how do i get it the formula to limit itself to just the original selection, even if i insert columns in the selection? thank you, jat |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
averages for cells
Column A has data (category for items in column B).
jat "Glenn" wrote: Put your formula in A1 and then insert a column at G (per the OP's instructions). Now what's in A1? FSt1 wrote: hi you need to understand the differenct between relative reference and absolute reference. look that up in xl help i think in your situation you want absolute reference or reference that don't change. relative references change when you move them or move the formula. to change form relatitive to absolute references and dollar signs i.e. =(SUM($G$3:$O$3)-MIN($G$3:$O$3)-MAX($G$3:$O$3))/7 you can put absolute reference on column, row, both. xl help will explain all that. regards FSt1 "jatman" wrote: i have a spreadsheet that calculates averages, but as i add more columns, it always calculates the same columns: B F G H I J ITEM AVG # ## # # the formula that i am using is =(SUM(G3:O3)-MIN(G3:O3)-MAX(G3:O3))/7 (drops the lowest and highest and make an olympic average to use). when i insert another row of the most recent data on the left side (push old data to the right, but keep the information). i insert a column between f and g (new column is now g...). the formula changes and continues to use the same values and not the new values... formula changes to =sum(h3:o3)-min(h3:o3)-max(h3:o3))/7 how do i get it the formula to limit itself to just the original selection, even if i insert columns in the selection? thank you, jat |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
averages for cells
My response was for FSt1, whose recommendation didn't work. Please see my other
response for something that will work. Sorry for the confusion. jatman wrote: Column A has data (category for items in column B). jat "Glenn" wrote: Put your formula in A1 and then insert a column at G (per the OP's instructions). Now what's in A1? FSt1 wrote: hi you need to understand the differenct between relative reference and absolute reference. look that up in xl help i think in your situation you want absolute reference or reference that don't change. relative references change when you move them or move the formula. to change form relatitive to absolute references and dollar signs i.e. =(SUM($G$3:$O$3)-MIN($G$3:$O$3)-MAX($G$3:$O$3))/7 you can put absolute reference on column, row, both. xl help will explain all that. regards FSt1 "jatman" wrote: i have a spreadsheet that calculates averages, but as i add more columns, it always calculates the same columns: B F G H I J ITEM AVG # ## # # the formula that i am using is =(SUM(G3:O3)-MIN(G3:O3)-MAX(G3:O3))/7 (drops the lowest and highest and make an olympic average to use). when i insert another row of the most recent data on the left side (push old data to the right, but keep the information). i insert a column between f and g (new column is now g...). the formula changes and continues to use the same values and not the new values... formula changes to =sum(h3:o3)-min(h3:o3)-max(h3:o3))/7 how do i get it the formula to limit itself to just the original selection, even if i insert columns in the selection? thank you, jat |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| averages that ignore #VALUE! cells | Excel Discussion (Misc queries) | |||
| Averages with blank cells and specified criteria | New Users to Excel | |||
| compare adjacent cells and calculate averages | Excel Worksheet Functions | |||
| Dividing Cells & Averages | Excel Discussion (Misc queries) | |||
| Averages that include cells with IF statements | Excel Worksheet Functions | |||
Linear Mode
