Averages

[Zygy]

Is there a formula by which when I have say e.g. four entries in a column
on lines 31,32,33 & 34 I can obtain a average of these entries in line 35?
If there is how do I arrange matters so that if I add entries above line 35
this line will automatically adjust itself to show a new average?

[tony h]

try

=SUM(A31:A99)/COUNTA(A31:A99)

This formula is using data in column A and allows you to fill in values between rows 31 and 99 without needing to change theformula

hope this helps

[Zygy]

Many thanks for the reply. I am affraid that this formula did not produce
the result for me. I have these entries in line 31-34 Col. G:-

40.66
35.97
38.86
38.88
the average should be 38.59, but with the formula:- =Sum (G31-G99)/COUNTA
(G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> try
>
> =SUM(A31:A99)/COUNTA(A31:A99)
>
> This formula is using data in column A and allows you to fill in values
> between rows 31 and 99 without needing to change theformula
>
> hope this helps
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=552892
>

[CLR]

Might be a type-o somewheres..........it appears to work fine for me,
returning 38.5925 with your given data/location

Vaya con Dios,
Chuck, CABGx3



"Zygy" <[email protected]> wrote in message
news:unFER#[email protected]...
> Many thanks for the reply. I am affraid that this formula did not produce
> the result for me. I have these entries in line 31-34 Col. G:-
>
> 40.66
> 35.97
> 38.86
> 38.88
> the average should be 38.59, but with the formula:- =Sum (G31-G99)/COUNTA
> (G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
> "tony h" <[email protected]> wrote in
> message news:[email protected]...
> >
> > try
> >
> > =SUM(A31:A99)/COUNTA(A31:A99)
> >
> > This formula is using data in column A and allows you to fill in values
> > between rows 31 and 99 without needing to change theformula
> >
> > hope this helps
> >
> >
> > --
> > tony h
> > ------------------------------------------------------------------------
> > tony h's Profile:
> > http://www.excelforum.com/member.php...o&userid=21074
> > View this thread:
http://www.excelforum.com/showthread...hreadid=552892
> >
>
>

[RagDyeR]

Replace your minus signs,

=SUM(G31-G99)/COUNTA(G31-G99)

with colons:

=SUM(G31:G99)/COUNTA(G31:G99)
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Zygy" <[email protected]> wrote in message
news:unFER%[email protected]...
Many thanks for the reply. I am affraid that this formula did not produce
the result for me. I have these entries in line 31-34 Col. G:-

40.66
35.97
38.86
38.88
the average should be 38.59, but with the formula:- =Sum (G31-G99)/COUNTA
(G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
"tony h" <[email protected]> wrote in
message news:[email protected]...
>
> try
>
> =SUM(A31:A99)/COUNTA(A31:A99)
>
> This formula is using data in column A and allows you to fill in values
> between rows 31 and 99 without needing to change theformula
>
> hope this helps
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile:
> http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=552892
>

[Zygy]

Thank you all for the contributions. Unfortunately I still cannot get the
right answer.When I use the semicolon instead of the minus I get a notice
stating that I made a mistake and will I accept the correction to the
Formula made by Excel, which is exactly the same as the one I typed in. When
I click OK I get another notice stating that Excel cannot carry out the
calculation, because I created a "Circular Reference"! My OS is Win.XPPro
(SP2) and Office Pro 2003.

Any further suggestions will be gratefully received.
"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> Replace your minus signs,
>
> =SUM(G31-G99)/COUNTA(G31-G99)
>
> with colons:
>
> =SUM(G31:G99)/COUNTA(G31:G99)
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Zygy" <[email protected]> wrote in message
> news:unFER%[email protected]...
> Many thanks for the reply. I am affraid that this formula did not produce
> the result for me. I have these entries in line 31-34 Col. G:-
>
> 40.66
> 35.97
> 38.86
> 38.88
> the average should be 38.59, but with the formula:- =Sum (G31-G99)/COUNTA
> (G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
> "tony h" <[email protected]> wrote in
> message news:[email protected]...
>>
>> try
>>
>> =SUM(A31:A99)/COUNTA(A31:A99)
>>
>> This formula is using data in column A and allows you to fill in values
>> between rows 31 and 99 without needing to change theformula
>>
>> hope this helps
>>
>>
>> --
>> tony h
>> ------------------------------------------------------------------------
>> tony h's Profile:
>> http://www.excelforum.com/member.php...o&userid=21074
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=552892
>>
>
>
>

[George Gee]

Zygy

Use colons, not semi-colons!
Do not place the formula in any cell referenced within the formula!
Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
Does this help?

George Gee


"Zygy" <[email protected]> wrote in message
news:OC%[email protected]...
> Thank you all for the contributions. Unfortunately I still cannot get the
> right answer.When I use the semicolon instead of the minus I get a notice
> stating that I made a mistake and will I accept the correction to the
> Formula made by Excel, which is exactly the same as the one I typed in.
> When I click OK I get another notice stating that Excel cannot carry out
> the calculation, because I created a "Circular Reference"! My OS is
> Win.XPPro (SP2) and Office Pro 2003.
>
> Any further suggestions will be gratefully received.
> "RagDyeR" <[email protected]> wrote in message
> news:[email protected]...
>> Replace your minus signs,
>>
>> =SUM(G31-G99)/COUNTA(G31-G99)
>>
>> with colons:
>>
>> =SUM(G31:G99)/COUNTA(G31:G99)
>> --
>>
>> HTH,
>>
>> RD
>> =====================================================
>> Please keep all correspondence within the Group, so all may benefit!
>> =====================================================
>>
>> "Zygy" <[email protected]> wrote in message
>> news:unFER%[email protected]...
>> Many thanks for the reply. I am affraid that this formula did not produce
>> the result for me. I have these entries in line 31-34 Col. G:-
>>
>> 40.66
>> 35.97
>> 38.86
>> 38.88
>> the average should be 38.59, but with the formula:- =Sum (G31-G99)/COUNTA
>> (G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
>> "tony h" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> try
>>>
>>> =SUM(A31:A99)/COUNTA(A31:A99)
>>>
>>> This formula is using data in column A and allows you to fill in values
>>> between rows 31 and 99 without needing to change theformula
>>>
>>> hope this helps
>>>
>>>
>>> --
>>> tony h
>>> ------------------------------------------------------------------------
>>> tony h's Profile:
>>> http://www.excelforum.com/member.php...o&userid=21074
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=552892
>>>
>>
>>
>>
>
>

[Zygy]

Thank you George for solving this problem. However, I always did put colons
in the formula, but I highlighted the total in G line 36 as all the other
totals of Cols. A-F!

Is there a formula that would put the average of the entries in Col.G one
line below the last entry in line with the other totals?

"George Gee" <[email protected]> wrote in message
news:[email protected]...
> Zygy
>
> Use colons, not semi-colons!
> Do not place the formula in any cell referenced within the formula!
> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
> Does this help?
>
> George Gee
>
>
> "Zygy" <[email protected]> wrote in message
> news:OC%[email protected]...
>> Thank you all for the contributions. Unfortunately I still cannot get the
>> right answer.When I use the semicolon instead of the minus I get a notice
>> stating that I made a mistake and will I accept the correction to the
>> Formula made by Excel, which is exactly the same as the one I typed in.
>> When I click OK I get another notice stating that Excel cannot carry out
>> the calculation, because I created a "Circular Reference"! My OS is
>> Win.XPPro (SP2) and Office Pro 2003.
>>
>> Any further suggestions will be gratefully received.
>> "RagDyeR" <[email protected]> wrote in message
>> news:[email protected]...
>>> Replace your minus signs,
>>>
>>> =SUM(G31-G99)/COUNTA(G31-G99)
>>>
>>> with colons:
>>>
>>> =SUM(G31:G99)/COUNTA(G31:G99)
>>> --
>>>
>>> HTH,
>>>
>>> RD
>>> =====================================================
>>> Please keep all correspondence within the Group, so all may benefit!
>>> =====================================================
>>>
>>> "Zygy" <[email protected]> wrote in message
>>> news:unFER%[email protected]...
>>> Many thanks for the reply. I am affraid that this formula did not
>>> produce
>>> the result for me. I have these entries in line 31-34 Col. G:-
>>>
>>> 40.66
>>> 35.97
>>> 38.86
>>> 38.88
>>> the average should be 38.59, but with the formula:- =Sum
>>> (G31-G99)/COUNTA
>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
>>> "tony h" <[email protected]> wrote in
>>> message news:[email protected]...
>>>>
>>>> try
>>>>
>>>> =SUM(A31:A99)/COUNTA(A31:A99)
>>>>
>>>> This formula is using data in column A and allows you to fill in values
>>>> between rows 31 and 99 without needing to change theformula
>>>>
>>>> hope this helps
>>>>
>>>>
>>>> --
>>>> tony h
>>>> ------------------------------------------------------------------------
>>>> tony h's Profile:
>>>> http://www.excelforum.com/member.php...o&userid=21074
>>>> View this thread:
>>>> http://www.excelforum.com/showthread...hreadid=552892
>>>>
>>>
>>>
>>>
>>
>>
>
>
>

[George Gee]

Zygy

What do you mean by "but I highlighted the total in G line 36"?
Do you mean that you put the formula there?
What formula have you got in G36?

George Gee


"Zygy" <[email protected]> wrote in message
news:[email protected]...
> Thank you George for solving this problem. However, I always did put
> colons in the formula, but I highlighted the total in G line 36 as all
> the other totals of Cols. A-F!
>
> Is there a formula that would put the average of the entries in Col.G one
> line below the last entry in line with the other totals?
>
> "George Gee" <[email protected]> wrote in message
> news:[email protected]...
>> Zygy
>>
>> Use colons, not semi-colons!
>> Do not place the formula in any cell referenced within the formula!
>> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
>> Does this help?
>>
>> George Gee
>>
>>
>> "Zygy" <[email protected]> wrote in message
>> news:OC%[email protected]...
>>> Thank you all for the contributions. Unfortunately I still cannot get
>>> the
>>> right answer.When I use the semicolon instead of the minus I get a
>>> notice
>>> stating that I made a mistake and will I accept the correction to the
>>> Formula made by Excel, which is exactly the same as the one I typed in.
>>> When I click OK I get another notice stating that Excel cannot carry out
>>> the calculation, because I created a "Circular Reference"! My OS is
>>> Win.XPPro (SP2) and Office Pro 2003.
>>>
>>> Any further suggestions will be gratefully received.
>>> "RagDyeR" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Replace your minus signs,
>>>>
>>>> =SUM(G31-G99)/COUNTA(G31-G99)
>>>>
>>>> with colons:
>>>>
>>>> =SUM(G31:G99)/COUNTA(G31:G99)
>>>> --
>>>>
>>>> HTH,
>>>>
>>>> RD
>>>> =====================================================
>>>> Please keep all correspondence within the Group, so all may benefit!
>>>> =====================================================
>>>>
>>>> "Zygy" <[email protected]> wrote in message
>>>> news:unFER%[email protected]...
>>>> Many thanks for the reply. I am affraid that this formula did not
>>>> produce
>>>> the result for me. I have these entries in line 31-34 Col. G:-
>>>>
>>>> 40.66
>>>> 35.97
>>>> 38.86
>>>> 38.88
>>>> the average should be 38.59, but with the formula:- =Sum
>>>> (G31-G99)/COUNTA
>>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
>>>> "tony h" <[email protected]> wrote in
>>>> message news:[email protected]...
>>>>>
>>>>> try
>>>>>
>>>>> =SUM(A31:A99)/COUNTA(A31:A99)
>>>>>
>>>>> This formula is using data in column A and allows you to fill in
>>>>> values
>>>>> between rows 31 and 99 without needing to change theformula
>>>>>
>>>>> hope this helps
>>>>>
>>>>>
>>>>> --
>>>>> tony h
>>>>> ------------------------------------------------------------------------
>>>>> tony h's Profile:
>>>>> http://www.excelforum.com/member.php...o&userid=21074
>>>>> View this thread:
>>>>> http://www.excelforum.com/showthread...hreadid=552892
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>
>

[Zygy]

I did not have anything, but I wanted the average to appear there. My
question is:- Is there a formula where the average of the entries in a
column will appear on a line below the last entry in that column, because
the formula you gave me only works if the answer is in the next column,
which is of no use to me, because the next column is used for other entries.
"George Gee" <[email protected]> wrote in message
news:uM%[email protected]...
> Zygy
>
> What do you mean by "but I highlighted the total in G line 36"?
> Do you mean that you put the formula there?
> What formula have you got in G36?
>
> George Gee
>
>
> "Zygy" <[email protected]> wrote in message
> news:[email protected]...
>> Thank you George for solving this problem. However, I always did put
>> colons in the formula, but I highlighted the total in G line 36 as all
>> the other totals of Cols. A-F!
>>
>> Is there a formula that would put the average of the entries in Col.G one
>> line below the last entry in line with the other totals?
>>
>> "George Gee" <[email protected]> wrote in message
>> news:[email protected]...
>>> Zygy
>>>
>>> Use colons, not semi-colons!
>>> Do not place the formula in any cell referenced within the formula!
>>> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
>>> Does this help?
>>>
>>> George Gee
>>>
>>>
>>> "Zygy" <[email protected]> wrote in message
>>> news:OC%[email protected]...
>>>> Thank you all for the contributions. Unfortunately I still cannot get
>>>> the
>>>> right answer.When I use the semicolon instead of the minus I get a
>>>> notice
>>>> stating that I made a mistake and will I accept the correction to the
>>>> Formula made by Excel, which is exactly the same as the one I typed in.
>>>> When I click OK I get another notice stating that Excel cannot carry
>>>> out
>>>> the calculation, because I created a "Circular Reference"! My OS is
>>>> Win.XPPro (SP2) and Office Pro 2003.
>>>>
>>>> Any further suggestions will be gratefully received.
>>>> "RagDyeR" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> Replace your minus signs,
>>>>>
>>>>> =SUM(G31-G99)/COUNTA(G31-G99)
>>>>>
>>>>> with colons:
>>>>>
>>>>> =SUM(G31:G99)/COUNTA(G31:G99)
>>>>> --
>>>>>
>>>>> HTH,
>>>>>
>>>>> RD
>>>>> =====================================================
>>>>> Please keep all correspondence within the Group, so all may benefit!
>>>>> =====================================================
>>>>>
>>>>> "Zygy" <[email protected]> wrote in message
>>>>> news:unFER%[email protected]...
>>>>> Many thanks for the reply. I am affraid that this formula did not
>>>>> produce
>>>>> the result for me. I have these entries in line 31-34 Col. G:-
>>>>>
>>>>> 40.66
>>>>> 35.97
>>>>> 38.86
>>>>> 38.88
>>>>> the average should be 38.59, but with the formula:- =Sum
>>>>> (G31-G99)/COUNTA
>>>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
>>>>> "tony h" <[email protected]> wrote in
>>>>> message news:[email protected]...
>>>>>>
>>>>>> try
>>>>>>
>>>>>> =SUM(A31:A99)/COUNTA(A31:A99)
>>>>>>
>>>>>> This formula is using data in column A and allows you to fill in
>>>>>> values
>>>>>> between rows 31 and 99 without needing to change theformula
>>>>>>
>>>>>> hope this helps
>>>>>>
>>>>>>
>>>>>> --
>>>>>> tony h
>>>>>> ------------------------------------------------------------------------
>>>>>> tony h's Profile:
>>>>>> http://www.excelforum.com/member.php...o&userid=21074
>>>>>> View this thread:
>>>>>> http://www.excelforum.com/showthread...hreadid=552892
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>

[CLR]

Maybe this modification of tony's suggestion, in G36.........

=SUM(G31:G35)/COUNTA(G31:G35)

Vaya con Dios,
Chuck, CABGx3



"Zygy" <[email protected]> wrote in message
news:[email protected]...
> I did not have anything, but I wanted the average to appear there. My
> question is:- Is there a formula where the average of the entries in a
> column will appear on a line below the last entry in that column,
because
> the formula you gave me only works if the answer is in the next column,
> which is of no use to me, because the next column is used for other
entries.
> "George Gee" <[email protected]> wrote in message
> news:uM%[email protected]...
> > Zygy
> >
> > What do you mean by "but I highlighted the total in G line 36"?
> > Do you mean that you put the formula there?
> > What formula have you got in G36?
> >
> > George Gee
> >
> >
> > "Zygy" <[email protected]> wrote in message
> > news:[email protected]...
> >> Thank you George for solving this problem. However, I always did put
> >> colons in the formula, but I highlighted the total in G line 36 as all
> >> the other totals of Cols. A-F!
> >>
> >> Is there a formula that would put the average of the entries in Col.G
one
> >> line below the last entry in line with the other totals?
> >>
> >> "George Gee" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> Zygy
> >>>
> >>> Use colons, not semi-colons!
> >>> Do not place the formula in any cell referenced within the formula!
> >>> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
> >>> Does this help?
> >>>
> >>> George Gee
> >>>
> >>>
> >>> "Zygy" <[email protected]> wrote in message
> >>> news:OC%[email protected]...
> >>>> Thank you all for the contributions. Unfortunately I still cannot get
> >>>> the
> >>>> right answer.When I use the semicolon instead of the minus I get a
> >>>> notice
> >>>> stating that I made a mistake and will I accept the correction to the
> >>>> Formula made by Excel, which is exactly the same as the one I typed
in.
> >>>> When I click OK I get another notice stating that Excel cannot carry
> >>>> out
> >>>> the calculation, because I created a "Circular Reference"! My OS is
> >>>> Win.XPPro (SP2) and Office Pro 2003.
> >>>>
> >>>> Any further suggestions will be gratefully received.
> >>>> "RagDyeR" <[email protected]> wrote in message
> >>>> news:[email protected]...
> >>>>> Replace your minus signs,
> >>>>>
> >>>>> =SUM(G31-G99)/COUNTA(G31-G99)
> >>>>>
> >>>>> with colons:
> >>>>>
> >>>>> =SUM(G31:G99)/COUNTA(G31:G99)
> >>>>> --
> >>>>>
> >>>>> HTH,
> >>>>>
> >>>>> RD
> >>>>> =====================================================
> >>>>> Please keep all correspondence within the Group, so all may benefit!
> >>>>> =====================================================
> >>>>>
> >>>>> "Zygy" <[email protected]> wrote in message
> >>>>> news:unFER%[email protected]...
> >>>>> Many thanks for the reply. I am affraid that this formula did not
> >>>>> produce
> >>>>> the result for me. I have these entries in line 31-34 Col. G:-
> >>>>>
> >>>>> 40.66
> >>>>> 35.97
> >>>>> 38.86
> >>>>> 38.88
> >>>>> the average should be 38.59, but with the formula:- =Sum
> >>>>> (G31-G99)/COUNTA
> >>>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go
wrong?
> >>>>> "tony h" <[email protected]> wrote
in
> >>>>> message news:[email protected]...
> >>>>>>
> >>>>>> try
> >>>>>>
> >>>>>> =SUM(A31:A99)/COUNTA(A31:A99)
> >>>>>>
> >>>>>> This formula is using data in column A and allows you to fill in
> >>>>>> values
> >>>>>> between rows 31 and 99 without needing to change theformula
> >>>>>>
> >>>>>> hope this helps
> >>>>>>
> >>>>>>
> >>>>>> --
> >>>>>> tony h
>
>>>>>> ---------------------------------------------------------------------
---
> >>>>>> tony h's Profile:
> >>>>>> http://www.excelforum.com/member.php...o&userid=21074
> >>>>>> View this thread:
> >>>>>> http://www.excelforum.com/showthread...hreadid=552892
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>

[George Gee]

Zygy

Copy this formula, and paste it in cell G36
=SUM(G3:G35)/COUNTA(G3:G35)
Is this what you want?

George Gee

"Zygy" <[email protected]> wrote in message
news:[email protected]...
>I did not have anything, but I wanted the average to appear there. My
>question is:- Is there a formula where the average of the entries in a
>column will appear on a line below the last entry in that column, because
>the formula you gave me only works if the answer is in the next column,
>which is of no use to me, because the next column is used for other
>entries.
> "George Gee" <[email protected]> wrote in message
> news:uM%[email protected]...
>> Zygy
>>
>> What do you mean by "but I highlighted the total in G line 36"?
>> Do you mean that you put the formula there?
>> What formula have you got in G36?
>>
>> George Gee
>>
>>
>> "Zygy" <[email protected]> wrote in message
>> news:[email protected]...
>>> Thank you George for solving this problem. However, I always did put
>>> colons in the formula, but I highlighted the total in G line 36 as all
>>> the other totals of Cols. A-F!
>>>
>>> Is there a formula that would put the average of the entries in Col.G
>>> one line below the last entry in line with the other totals?
>>>
>>> "George Gee" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Zygy
>>>>
>>>> Use colons, not semi-colons!
>>>> Do not place the formula in any cell referenced within the formula!
>>>> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
>>>> Does this help?
>>>>
>>>> George Gee
>>>>
>>>>
>>>> "Zygy" <[email protected]> wrote in message
>>>> news:OC%[email protected]...
>>>>> Thank you all for the contributions. Unfortunately I still cannot get
>>>>> the
>>>>> right answer.When I use the semicolon instead of the minus I get a
>>>>> notice
>>>>> stating that I made a mistake and will I accept the correction to the
>>>>> Formula made by Excel, which is exactly the same as the one I typed
>>>>> in.
>>>>> When I click OK I get another notice stating that Excel cannot carry
>>>>> out
>>>>> the calculation, because I created a "Circular Reference"! My OS is
>>>>> Win.XPPro (SP2) and Office Pro 2003.
>>>>>
>>>>> Any further suggestions will be gratefully received.
>>>>> "RagDyeR" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> Replace your minus signs,
>>>>>>
>>>>>> =SUM(G31-G99)/COUNTA(G31-G99)
>>>>>>
>>>>>> with colons:
>>>>>>
>>>>>> =SUM(G31:G99)/COUNTA(G31:G99)
>>>>>> --
>>>>>>
>>>>>> HTH,
>>>>>>
>>>>>> RD
>>>>>> =====================================================
>>>>>> Please keep all correspondence within the Group, so all may benefit!
>>>>>> =====================================================
>>>>>>
>>>>>> "Zygy" <[email protected]> wrote in message
>>>>>> news:unFER%[email protected]...
>>>>>> Many thanks for the reply. I am affraid that this formula did not
>>>>>> produce
>>>>>> the result for me. I have these entries in line 31-34 Col. G:-
>>>>>>
>>>>>> 40.66
>>>>>> 35.97
>>>>>> 38.86
>>>>>> 38.88
>>>>>> the average should be 38.59, but with the formula:- =Sum
>>>>>> (G31-G99)/COUNTA
>>>>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go wrong?
>>>>>> "tony h" <[email protected]> wrote
>>>>>> in
>>>>>> message news:[email protected]...
>>>>>>>
>>>>>>> try
>>>>>>>
>>>>>>> =SUM(A31:A99)/COUNTA(A31:A99)
>>>>>>>
>>>>>>> This formula is using data in column A and allows you to fill in
>>>>>>> values
>>>>>>> between rows 31 and 99 without needing to change theformula
>>>>>>>
>>>>>>> hope this helps
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> tony h
>>>>>>> ------------------------------------------------------------------------
>>>>>>> tony h's Profile:
>>>>>>> http://www.excelforum.com/member.php...o&userid=21074
>>>>>>> View this thread:
>>>>>>> http://www.excelforum.com/showthread...hreadid=552892
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[George Gee]

Chuck

I thought about posting that formula, but it does
not cater for Zygy's first post of being able to
"add entries above line 35" (his wording).
So I suggested the range G3:G35.
Hope we get there soon!

George Gee

"CLR" <[email protected]> wrote in message
news:[email protected]...
> Maybe this modification of tony's suggestion, in G36.........
>
> =SUM(G31:G35)/COUNTA(G31:G35)
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Zygy" <[email protected]> wrote in message
> news:[email protected]...
>> I did not have anything, but I wanted the average to appear there. My
>> question is:- Is there a formula where the average of the entries in a
>> column will appear on a line below the last entry in that column,
> because
>> the formula you gave me only works if the answer is in the next column,
>> which is of no use to me, because the next column is used for other
> entries.
>> "George Gee" <[email protected]> wrote in message
>> news:uM%[email protected]...
>> > Zygy
>> >
>> > What do you mean by "but I highlighted the total in G line 36"?
>> > Do you mean that you put the formula there?
>> > What formula have you got in G36?
>> >
>> > George Gee
>> >
>> >
>> > "Zygy" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Thank you George for solving this problem. However, I always did put
>> >> colons in the formula, but I highlighted the total in G line 36 as
>> >> all
>> >> the other totals of Cols. A-F!
>> >>
>> >> Is there a formula that would put the average of the entries in Col.G
> one
>> >> line below the last entry in line with the other totals?
>> >>
>> >> "George Gee" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>> Zygy
>> >>>
>> >>> Use colons, not semi-colons!
>> >>> Do not place the formula in any cell referenced within the formula!
>> >>> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
>> >>> Does this help?
>> >>>
>> >>> George Gee
>> >>>
>> >>>
>> >>> "Zygy" <[email protected]> wrote in message
>> >>> news:OC%[email protected]...
>> >>>> Thank you all for the contributions. Unfortunately I still cannot
>> >>>> get
>> >>>> the
>> >>>> right answer.When I use the semicolon instead of the minus I get a
>> >>>> notice
>> >>>> stating that I made a mistake and will I accept the correction to
>> >>>> the
>> >>>> Formula made by Excel, which is exactly the same as the one I typed
> in.
>> >>>> When I click OK I get another notice stating that Excel cannot carry
>> >>>> out
>> >>>> the calculation, because I created a "Circular Reference"! My OS is
>> >>>> Win.XPPro (SP2) and Office Pro 2003.
>> >>>>
>> >>>> Any further suggestions will be gratefully received.
>> >>>> "RagDyeR" <[email protected]> wrote in message
>> >>>> news:[email protected]...
>> >>>>> Replace your minus signs,
>> >>>>>
>> >>>>> =SUM(G31-G99)/COUNTA(G31-G99)
>> >>>>>
>> >>>>> with colons:
>> >>>>>
>> >>>>> =SUM(G31:G99)/COUNTA(G31:G99)
>> >>>>> --
>> >>>>>
>> >>>>> HTH,
>> >>>>>
>> >>>>> RD
>> >>>>> =====================================================
>> >>>>> Please keep all correspondence within the Group, so all may
>> >>>>> benefit!
>> >>>>> =====================================================
>> >>>>>
>> >>>>> "Zygy" <[email protected]> wrote in message
>> >>>>> news:unFER%[email protected]...
>> >>>>> Many thanks for the reply. I am affraid that this formula did not
>> >>>>> produce
>> >>>>> the result for me. I have these entries in line 31-34 Col. G:-
>> >>>>>
>> >>>>> 40.66
>> >>>>> 35.97
>> >>>>> 38.86
>> >>>>> 38.88
>> >>>>> the average should be 38.59, but with the formula:- =Sum
>> >>>>> (G31-G99)/COUNTA
>> >>>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go
> wrong?
>> >>>>> "tony h" <[email protected]> wrote
> in
>> >>>>> message news:[email protected]...
>> >>>>>>
>> >>>>>> try
>> >>>>>>
>> >>>>>> =SUM(A31:A99)/COUNTA(A31:A99)
>> >>>>>>
>> >>>>>> This formula is using data in column A and allows you to fill in
>> >>>>>> values
>> >>>>>> between rows 31 and 99 without needing to change theformula
>> >>>>>>
>> >>>>>> hope this helps
>> >>>>>>
>> >>>>>>
>> >>>>>> --
>> >>>>>> tony h
>>
>>>>>>> ---------------------------------------------------------------------
> ---
>> >>>>>> tony h's Profile:
>> >>>>>> http://www.excelforum.com/member.php...o&userid=21074
>> >>>>>> View this thread:
>> >>>>>> http://www.excelforum.com/showthread...hreadid=552892
>> >>>>>>
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>
>> >>>>
>> >>>
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Zygy]

I did that before you suggested it but if you look up my first posting you
will see that your suggestion will not work AUTOMATICALLY if I add further
entries on lines 36, 37, 38 ect, ect.. The original formula suggested was
=SUM (G31:G99)/COUNT (G31:G99), which would work if I added further entries
in line 36, 37, ect. but where am I supposed to get the resulting average
then? I have a feeling that there is no solution to my problem. Do all the
contributors agree?
"George Gee" <[email protected]> wrote in message
news:[email protected]...
> Zygy
>
> Copy this formula, and paste it in cell G36
> =SUM(G3:G35)/COUNTA(G3:G35)
> Is this what you want?
>
> George Gee
>
> "Zygy" <[email protected]> wrote in message
> news:[email protected]...
>>I did not have anything, but I wanted the average to appear there. My
>>question is:- Is there a formula where the average of the entries in a
>>column will appear on a line below the last entry in that column,
>>because the formula you gave me only works if the answer is in the next
>>column, which is of no use to me, because the next column is used for
>>other entries.
>> "George Gee" <[email protected]> wrote in message
>> news:uM%[email protected]...
>>> Zygy
>>>
>>> What do you mean by "but I highlighted the total in G line 36"?
>>> Do you mean that you put the formula there?
>>> What formula have you got in G36?
>>>
>>> George Gee
>>>
>>>
>>> "Zygy" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Thank you George for solving this problem. However, I always did put
>>>> colons in the formula, but I highlighted the total in G line 36 as all
>>>> the other totals of Cols. A-F!
>>>>
>>>> Is there a formula that would put the average of the entries in Col.G
>>>> one line below the last entry in line with the other totals?
>>>>
>>>> "George Gee" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> Zygy
>>>>>
>>>>> Use colons, not semi-colons!
>>>>> Do not place the formula in any cell referenced within the formula!
>>>>> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
>>>>> Does this help?
>>>>>
>>>>> George Gee
>>>>>
>>>>>
>>>>> "Zygy" <[email protected]> wrote in message
>>>>> news:OC%[email protected]...
>>>>>> Thank you all for the contributions. Unfortunately I still cannot get
>>>>>> the
>>>>>> right answer.When I use the semicolon instead of the minus I get a
>>>>>> notice
>>>>>> stating that I made a mistake and will I accept the correction to the
>>>>>> Formula made by Excel, which is exactly the same as the one I typed
>>>>>> in.
>>>>>> When I click OK I get another notice stating that Excel cannot carry
>>>>>> out
>>>>>> the calculation, because I created a "Circular Reference"! My OS is
>>>>>> Win.XPPro (SP2) and Office Pro 2003.
>>>>>>
>>>>>> Any further suggestions will be gratefully received.
>>>>>> "RagDyeR" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> Replace your minus signs,
>>>>>>>
>>>>>>> =SUM(G31-G99)/COUNTA(G31-G99)
>>>>>>>
>>>>>>> with colons:
>>>>>>>
>>>>>>> =SUM(G31:G99)/COUNTA(G31:G99)
>>>>>>> --
>>>>>>>
>>>>>>> HTH,
>>>>>>>
>>>>>>> RD
>>>>>>> =====================================================
>>>>>>> Please keep all correspondence within the Group, so all may benefit!
>>>>>>> =====================================================
>>>>>>>
>>>>>>> "Zygy" <[email protected]> wrote in message
>>>>>>> news:unFER%[email protected]...
>>>>>>> Many thanks for the reply. I am affraid that this formula did not
>>>>>>> produce
>>>>>>> the result for me. I have these entries in line 31-34 Col. G:-
>>>>>>>
>>>>>>> 40.66
>>>>>>> 35.97
>>>>>>> 38.86
>>>>>>> 38.88
>>>>>>> the average should be 38.59, but with the formula:- =Sum
>>>>>>> (G31-G99)/COUNTA
>>>>>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go
>>>>>>> wrong?
>>>>>>> "tony h" <[email protected]> wrote
>>>>>>> in
>>>>>>> message news:[email protected]...
>>>>>>>>
>>>>>>>> try
>>>>>>>>
>>>>>>>> =SUM(A31:A99)/COUNTA(A31:A99)
>>>>>>>>
>>>>>>>> This formula is using data in column A and allows you to fill in
>>>>>>>> values
>>>>>>>> between rows 31 and 99 without needing to change theformula
>>>>>>>>
>>>>>>>> hope this helps
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> tony h
>>>>>>>> ------------------------------------------------------------------------
>>>>>>>> tony h's Profile:
>>>>>>>> http://www.excelforum.com/member.php...o&userid=21074
>>>>>>>> View this thread:
>>>>>>>> http://www.excelforum.com/showthread...hreadid=552892
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[tony h]

Hi,

Zygy you may be right. But it is all about looking creatively at the problem. You can only do this from your position where you know how the spreadsheet is to be used, by whom, and how it will be updated.

A couple of suggestions:
1. I tend to put totals, averages etc to the top and left of a spreadsheet because you can then use the freeze-panes optyion to keep these on display while scrolling through the detail.

2. You could use blank rows (between say 35 and 99) and hide them. Only un-hiding when the rows need to be populated.

Hope this helps

[Zygy]

Many thanks to all who contributed to my problem. I will follow tony h's
advice and create my own solution, now that I have the averages formula.
Thank you all again and please consider this matter closed.
"George Gee" <[email protected]> wrote in message
news:[email protected]...
> Chuck
>
> I thought about posting that formula, but it does
> not cater for Zygy's first post of being able to
> "add entries above line 35" (his wording).
> So I suggested the range G3:G35.
> Hope we get there soon!
>
> George Gee
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
>> Maybe this modification of tony's suggestion, in G36.........
>>
>> =SUM(G31:G35)/COUNTA(G31:G35)
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>>
>>
>> "Zygy" <[email protected]> wrote in message
>> news:[email protected]...
>>> I did not have anything, but I wanted the average to appear there. My
>>> question is:- Is there a formula where the average of the entries in a
>>> column will appear on a line below the last entry in that column,
>> because
>>> the formula you gave me only works if the answer is in the next column,
>>> which is of no use to me, because the next column is used for other
>> entries.
>>> "George Gee" <[email protected]> wrote in message
>>> news:uM%[email protected]...
>>> > Zygy
>>> >
>>> > What do you mean by "but I highlighted the total in G line 36"?
>>> > Do you mean that you put the formula there?
>>> > What formula have you got in G36?
>>> >
>>> > George Gee
>>> >
>>> >
>>> > "Zygy" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> Thank you George for solving this problem. However, I always did put
>>> >> colons in the formula, but I highlighted the total in G line 36 as
>>> >> all
>>> >> the other totals of Cols. A-F!
>>> >>
>>> >> Is there a formula that would put the average of the entries in Col.G
>> one
>>> >> line below the last entry in line with the other totals?
>>> >>
>>> >> "George Gee" <[email protected]> wrote in message
>>> >> news:[email protected]...
>>> >>> Zygy
>>> >>>
>>> >>> Use colons, not semi-colons!
>>> >>> Do not place the formula in any cell referenced within the formula!
>>> >>> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
>>> >>> Does this help?
>>> >>>
>>> >>> George Gee
>>> >>>
>>> >>>
>>> >>> "Zygy" <[email protected]> wrote in message
>>> >>> news:OC%[email protected]...
>>> >>>> Thank you all for the contributions. Unfortunately I still cannot
>>> >>>> get
>>> >>>> the
>>> >>>> right answer.When I use the semicolon instead of the minus I get a
>>> >>>> notice
>>> >>>> stating that I made a mistake and will I accept the correction to
>>> >>>> the
>>> >>>> Formula made by Excel, which is exactly the same as the one I typed
>> in.
>>> >>>> When I click OK I get another notice stating that Excel cannot
>>> >>>> carry
>>> >>>> out
>>> >>>> the calculation, because I created a "Circular Reference"! My OS is
>>> >>>> Win.XPPro (SP2) and Office Pro 2003.
>>> >>>>
>>> >>>> Any further suggestions will be gratefully received.
>>> >>>> "RagDyeR" <[email protected]> wrote in message
>>> >>>> news:[email protected]...
>>> >>>>> Replace your minus signs,
>>> >>>>>
>>> >>>>> =SUM(G31-G99)/COUNTA(G31-G99)
>>> >>>>>
>>> >>>>> with colons:
>>> >>>>>
>>> >>>>> =SUM(G31:G99)/COUNTA(G31:G99)
>>> >>>>> --
>>> >>>>>
>>> >>>>> HTH,
>>> >>>>>
>>> >>>>> RD
>>> >>>>> =====================================================
>>> >>>>> Please keep all correspondence within the Group, so all may
>>> >>>>> benefit!
>>> >>>>> =====================================================
>>> >>>>>
>>> >>>>> "Zygy" <[email protected]> wrote in message
>>> >>>>> news:unFER%[email protected]...
>>> >>>>> Many thanks for the reply. I am affraid that this formula did not
>>> >>>>> produce
>>> >>>>> the result for me. I have these entries in line 31-34 Col. G:-
>>> >>>>>
>>> >>>>> 40.66
>>> >>>>> 35.97
>>> >>>>> 38.86
>>> >>>>> 38.88
>>> >>>>> the average should be 38.59, but with the formula:- =Sum
>>> >>>>> (G31-G99)/COUNTA
>>> >>>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go
>> wrong?
>>> >>>>> "tony h" <[email protected]>
>>> >>>>> wrote
>> in
>>> >>>>> message news:[email protected]...
>>> >>>>>>
>>> >>>>>> try
>>> >>>>>>
>>> >>>>>> =SUM(A31:A99)/COUNTA(A31:A99)
>>> >>>>>>
>>> >>>>>> This formula is using data in column A and allows you to fill in
>>> >>>>>> values
>>> >>>>>> between rows 31 and 99 without needing to change theformula
>>> >>>>>>
>>> >>>>>> hope this helps
>>> >>>>>>
>>> >>>>>>
>>> >>>>>> --
>>> >>>>>> tony h
>>>
>>>>>>>> ---------------------------------------------------------------------
>> ---
>>> >>>>>> tony h's Profile:
>>> >>>>>> http://www.excelforum.com/member.php...o&userid=21074
>>> >>>>>> View this thread:
>>> >>>>>> http://www.excelforum.com/showthread...hreadid=552892
>>> >>>>>>
>>> >>>>>
>>> >>>>>
>>> >>>>>
>>> >>>>
>>> >>>>
>>> >>>
>>> >>>
>>> >>>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

[CLR]

You're welcome Zygy, and please don't hesitate to post back if you run into
more problems..........

Vaya con Dios,
Chuck, CABGx3


"Zygy" <[email protected]> wrote in message
news:#[email protected]...
> Many thanks to all who contributed to my problem. I will follow tony h's
> advice and create my own solution, now that I have the averages formula.
> Thank you all again and please consider this matter closed.
> "George Gee" <[email protected]> wrote in message
> news:[email protected]...
> > Chuck
> >
> > I thought about posting that formula, but it does
> > not cater for Zygy's first post of being able to
> > "add entries above line 35" (his wording).
> > So I suggested the range G3:G35.
> > Hope we get there soon!
> >
> > George Gee
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> >> Maybe this modification of tony's suggestion, in G36.........
> >>
> >> =SUM(G31:G35)/COUNTA(G31:G35)
> >>
> >> Vaya con Dios,
> >> Chuck, CABGx3
> >>
> >>
> >>
> >> "Zygy" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> I did not have anything, but I wanted the average to appear there. My
> >>> question is:- Is there a formula where the average of the entries in a
> >>> column will appear on a line below the last entry in that column,
> >> because
> >>> the formula you gave me only works if the answer is in the next
column,
> >>> which is of no use to me, because the next column is used for other
> >> entries.
> >>> "George Gee" <[email protected]> wrote in message
> >>> news:uM%[email protected]...
> >>> > Zygy
> >>> >
> >>> > What do you mean by "but I highlighted the total in G line 36"?
> >>> > Do you mean that you put the formula there?
> >>> > What formula have you got in G36?
> >>> >
> >>> > George Gee
> >>> >
> >>> >
> >>> > "Zygy" <[email protected]> wrote in message
> >>> > news:[email protected]...
> >>> >> Thank you George for solving this problem. However, I always did
put
> >>> >> colons in the formula, but I highlighted the total in G line 36 as
> >>> >> all
> >>> >> the other totals of Cols. A-F!
> >>> >>
> >>> >> Is there a formula that would put the average of the entries in
Col.G
> >> one
> >>> >> line below the last entry in line with the other totals?
> >>> >>
> >>> >> "George Gee" <[email protected]> wrote in message
> >>> >> news:[email protected]...
> >>> >>> Zygy
> >>> >>>
> >>> >>> Use colons, not semi-colons!
> >>> >>> Do not place the formula in any cell referenced within the
formula!
> >>> >>> Try putting =SUM(G31:G99)/COUNTA(G31:G99) in H34
> >>> >>> Does this help?
> >>> >>>
> >>> >>> George Gee
> >>> >>>
> >>> >>>
> >>> >>> "Zygy" <[email protected]> wrote in message
> >>> >>> news:OC%[email protected]...
> >>> >>>> Thank you all for the contributions. Unfortunately I still cannot
> >>> >>>> get
> >>> >>>> the
> >>> >>>> right answer.When I use the semicolon instead of the minus I get
a
> >>> >>>> notice
> >>> >>>> stating that I made a mistake and will I accept the correction to
> >>> >>>> the
> >>> >>>> Formula made by Excel, which is exactly the same as the one I
typed
> >> in.
> >>> >>>> When I click OK I get another notice stating that Excel cannot
> >>> >>>> carry
> >>> >>>> out
> >>> >>>> the calculation, because I created a "Circular Reference"! My OS
is
> >>> >>>> Win.XPPro (SP2) and Office Pro 2003.
> >>> >>>>
> >>> >>>> Any further suggestions will be gratefully received.
> >>> >>>> "RagDyeR" <[email protected]> wrote in message
> >>> >>>> news:[email protected]...
> >>> >>>>> Replace your minus signs,
> >>> >>>>>
> >>> >>>>> =SUM(G31-G99)/COUNTA(G31-G99)
> >>> >>>>>
> >>> >>>>> with colons:
> >>> >>>>>
> >>> >>>>> =SUM(G31:G99)/COUNTA(G31:G99)
> >>> >>>>> --
> >>> >>>>>
> >>> >>>>> HTH,
> >>> >>>>>
> >>> >>>>> RD
> >>> >>>>> =====================================================
> >>> >>>>> Please keep all correspondence within the Group, so all may
> >>> >>>>> benefit!
> >>> >>>>> =====================================================
> >>> >>>>>
> >>> >>>>> "Zygy" <[email protected]> wrote in message
> >>> >>>>> news:unFER%[email protected]...
> >>> >>>>> Many thanks for the reply. I am affraid that this formula did
not
> >>> >>>>> produce
> >>> >>>>> the result for me. I have these entries in line 31-34 Col. G:-
> >>> >>>>>
> >>> >>>>> 40.66
> >>> >>>>> 35.97
> >>> >>>>> 38.86
> >>> >>>>> 38.88
> >>> >>>>> the average should be 38.59, but with the formula:- =Sum
> >>> >>>>> (G31-G99)/COUNTA
> >>> >>>>> (G31-G99) I am getting 40.66 - the first entry. Where did I go
> >> wrong?
> >>> >>>>> "tony h" <[email protected]>
> >>> >>>>> wrote
> >> in
> >>> >>>>> message
news:[email protected]...
> >>> >>>>>>
> >>> >>>>>> try
> >>> >>>>>>
> >>> >>>>>> =SUM(A31:A99)/COUNTA(A31:A99)
> >>> >>>>>>
> >>> >>>>>> This formula is using data in column A and allows you to fill
in
> >>> >>>>>> values
> >>> >>>>>> between rows 31 and 99 without needing to change theformula
> >>> >>>>>>
> >>> >>>>>> hope this helps
> >>> >>>>>>
> >>> >>>>>>
> >>> >>>>>> --
> >>> >>>>>> tony h
> >>>
>
>>>>>>>> -------------------------------------------------------------------
--
> >> ---
> >>> >>>>>> tony h's Profile:
> >>> >>>>>>
http://www.excelforum.com/member.php...o&userid=21074
> >>> >>>>>> View this thread:
> >>> >>>>>> http://www.excelforum.com/showthread...hreadid=552892
> >>> >>>>>>
> >>> >>>>>
> >>> >>>>>
> >>> >>>>>
> >>> >>>>
> >>> >>>>
> >>> >>>
> >>> >>>
> >>> >>>
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>