Formula Help Please

[Frank West]

Date x x x x x x
Tom Bob John Mary Ted Ron

Aver 81 93 95 83 89 83
12/9 78 79 90
12/10 73
12/11
12/12 77 99 91
12/13 95
12/14 86 89
12/15 95
12/16
12/17 76
12/18
12/19 87 99 83
12/20 87
12/21 84
12/23 100

Hi all!

How can I average the LAST three numbers (or fewer, if there are less
than 3) in each column and have the answer show up where I have an x
above the names?

I'd also like to be able to change the number I want to average from
to three to five or whatever.

As you can see I have some blank cells, and I will be addling new data
every day, although each column will not necessarily receive new data.

The Row of numbers next to Aver is the average of ALL the cells in
each row and would not be part of the calculations I am after.

Someone posted the following in answer to this question last week, and
I have been experimenting with it, but I can't get it to work.

Thanks to all, your help is greatly appreciated.

Frank West

 

[Don Guillett]

How about a nice macro. Just place cursor on desired column and execute.

Sub AverageDesiredNumber()
Dim x As Integer
ac = ActiveCell.Column
Lastrow = Cells(Rows.Count, ac).End(xlUp).Row
RowCount = Application.CountA(Range(Cells(2, ac), _
Cells(Lastrow, ac)))
x = InputBox("How many to average")
If x > RowCount Then x = RowCount
For i = Lastrow To 1 Step -1
If ctr < x Then
mysum = mysum + Cells(i, ac)
End If
If Cells(i, ac) <> "" Then ctr = ctr + 1
Next
Cells(1, ac) = mysum / x
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

Date x x x x x x
Tom Bob John Mary Ted Ron

Aver 81 93 95 83 89 83
12/9 78 79 90
12/10 73
12/11
12/12 77 99 91
12/13 95
12/14 86 89
12/15 95
12/16
12/17 76
12/18
12/19 87 99 83
12/20 87
12/21 84
12/23 100

Hi all!

How can I average the LAST three numbers (or fewer, if there are less
than 3) in each column and have the answer show up where I have an x
above the names?

I'd also like to be able to change the number I want to average from
to three to five or whatever.

As you can see I have some blank cells, and I will be addling new data
every day, although each column will not necessarily receive new data.

The Row of numbers next to Aver is the average of ALL the cells in
each row and would not be part of the calculations I am after.

Someone posted the following in answer to this question last week, and
I have been experimenting with it, but I can't get it to work.

chagne the first 2 in 2)-2,0 and the second 2 in 2)+2,1 to the number you
wish.<<

 

[Guest]

Frank
Enter the number of last scores you want averaged in cell I

Then cut and paste this following formula in cell B1

=AVERAGE(OFFSET(B5,LARGE(ISNUMBER(B5:B20)*ROW(B5:B20),IF(COUNTA(B5:B20)<$I$1,COUNTA(B5:B20),$I$1))-ROW(B5),0,LARGE(ISNUMBER(B5:B20)*ROW(B5:B20),1)-LARGE(ISNUMBER(B5:B20)*ROW(B5:B20),IF(COUNTA(B5:B20)<$I$1,COUNTA(B5:B20),$I$1))+1,1)

After you have entered the formula you need to hit -ctrl- -shift- -enter- instead of just enter, in order to make this an array formula

I devolped this formula by modifiying the formula you got from Harlan Grove

If you have any trouble, or want an explanation, repost on this threa

Good Luck
Mark Graesse
(e-mail address removed)

----- Frank West wrote: ----

Date x x x x x
Tom Bob John Mary Ted Ro

Aver 81 93 95 83 89 83
12/9 78 79 90
12/10 73
12/11
12/12 77 99 91
12/13 95
12/14 86 89
12/15 95
12/16
12/17 76
12/18
12/19 87 99 83
12/20 87
12/21 84
12/23 100

Hi all

How can I average the LAST three numbers (or fewer, if there are les
than 3) in each column and have the answer show up where I have an
above the names

I'd also like to be able to change the number I want to average fro
to three to five or whatever

As you can see I have some blank cells, and I will be addling new dat
every day, although each column will not necessarily receive new data

The Row of numbers next to Aver is the average of ALL the cells i
each row and would not be part of the calculations I am after

Someone posted the following in answer to this question last week, an
I have been experimenting with it, but I can't get it to work

Thanks to all, your help is greatly appreciated

Frank Wes

 

[Frank West]

Mark, I follow everything you say, except the part about entering the
number of last scores in cell l1. Do you mean cell L1, or i1 or just
exactly which cell. I've tried 10 different places, but can't seem to
get it to work. Thanks.

 

[Frank West]

Hi, Don. Thank you for this. I don't know the first thing about
macros. I tried using macros in the past and it always turned into a
real mess. I couldn't get rid of them and they infected other
workbooks. However, I still tried to use the one you posted, but I
think I screwed things up. How do I delete any macros I tried to
implement?

Thanks.

 

[Frank West]

Frank,
Enter the number of last scores you want averaged in cell I1

Mark, I think I've got it now. You mean i1. Since I'm going to put
more columns across my spreadsheet I can just change i1 to whatever
cell I want in the formula, right? Thanks, much.

Frank

 

[Don Guillett]

You should have copied the macro to a regular module. It assume that row 1
is where you want the total and that your data starts in row 2 and goes down
as in your example. Then you click anywhere in the column to set the focus
on that column and then execute the macro. Then you will be asked for a
number such as 4. Enter the number and the average will be placed in the top
row of that column.

However, Marks formula will be much better for you. Just be sure that it is
array entered using CONTROL+SHIFT+ENTER instead of just ENTER when you edit
or create the macro.

If none of this works for you, send me a SMALL workbook with a sample of
what you have.
Send to my email below. NEVER send a file to the ng.

 

[Don Guillett]

Mark's answer would be
Yes

 

[David McRitchie]

Hi Frank,
Shouldn't a serious problem to distinguish if I1 is really L1
or i1 because you can simply paste it into Excel
and look at it there use upperI() or lower() worksheet
function or a macro such as those on my proper.htm page.

It's a problem reading it, of course, depending on the font, but
you did answer you own question by some means before someone
else did.

Personal computers with email, web pages, or newsgroups makes
pasting a breeze and certainly
beats transcribing from a printed page, FAX, or phone conversation,
where transcription errers were so prevalent.

 

[Frank West]

You should have copied the macro to a regular module. It assume that row 1
is where you want the total and that your data starts in row 2 and goes down
as in your example. Then you click anywhere in the column to set the focus
on that column and then execute the macro. Then you will be asked for a
number such as 4. Enter the number and the average will be placed in the top
row of that column.

However, Marks formula will be much better for you. Just be sure that it is
array entered using CONTROL+SHIFT+ENTER instead of just ENTER when you edit
or create the macro.

If none of this works for you, send me a SMALL workbook with a sample of
what you have.
Send to my email below. NEVER send a file to the ng.

Thanks for your time, Don. I want to thank David and Mark as well.
Mark's formula works perfectly for my situation and I believe I was
able to delete the macro.

I'm going to use the macro that you sent me in a new workbook to try
and get a feel for how to use them. I have to learn how to do it
someday, so now is as good a time as any.

The problem is that I'm totally ignorant (module?), but I've seen some
web pages posted for help getting started with macros, and I'll just
play with it.

I appreciate everyone's help in this regard. The formula I was after
is something I could not have figured out on my own. (I tried for
about a week before I gave up.) I still don't understand everything
in the formula, some of the functions are new to me (isnumber, large,
row, counta, etc.), so I'll look those up too.

This is one of the most helpful newsgroups on the net.

Thanks to all,

Frank West