A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

averages for cells



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2008, 04:50 PM posted to microsoft.public.excel.worksheet.functions
jatman
external usenet poster
 
Posts: 102
Default 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(h33)-min(h33)-max(h33))/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  
Old September 23rd, 2008, 05:36 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default 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(h33)-min(h33)-max(h33))/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  
Old September 23rd, 2008, 05:43 PM posted to microsoft.public.excel.worksheet.functions
FSt1
external usenet poster
 
Posts: 2,788
Default 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(h33)-min(h33)-max(h33))/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  
Old September 23rd, 2008, 05:45 PM posted to microsoft.public.excel.worksheet.functions
klswvu[_2_]
external usenet poster
 
Posts: 27
Default 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(h33)-min(h33)-max(h33))/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  
Old September 23rd, 2008, 06:59 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default 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(h33)-min(h33)-max(h33))/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  
Old September 23rd, 2008, 07:15 PM posted to microsoft.public.excel.worksheet.functions
jatman
external usenet poster
 
Posts: 102
Default 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(h33)-min(h33)-max(h33))/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  
Old September 23rd, 2008, 07:24 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default 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(h33)-min(h33)-max(h33))/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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:26 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.