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  

"Average If Not Blank" Formula



 
 
Thread Tools Display Modes
  #11  
Old May 4th, 2004, 06:43 PM
CindyA
external usenet poster
 
Posts: n/a
Default "Average If Not Blank" Formula

Thanks, I'll give it a try.


-----Original Message-----
Hi
sure :-)
try the following array formula (entered with

CTRL+SHIFT+ENTER
=AVERAGE(OFFSET($J1,0,0,1,-(COLUMN($J1)-LARGE(IF

($A1:$J1"",COLUMN($A1
:$J1)),2)+1)))


--
Regards
Frank Kabel
Frankfurt, Germany


CindyA wrote:
Works great if all cells are filled in, but

unfortunately,
I periodically will have a blank cell in the mix. Any
solution to this?

Thanks in advance.

-----Original Message-----
Hi
do you want the average for a column or for a row.

Looking
at your other post I assume you want the average per
column (e.g. from A1:J1) BUT also considering to take

only
the last to filled cells.

I assume that you don't have blank cells in between per
row (e.g. if D1 is filled A1:C1 is filled also)

Try the following formula in cell K1
=AVERAGE(OFFSET($A1,COUNTA($A1:$J1)-1,1,-2))
and copy this down

-----Original Message-----
I have 10 columns and 5 rows of cells. All cells do

not
contain a number and the cells which DO contain

numbers
are sometimes 1, 2, or 3 digits. I need to enter a
formula to the right of the grid of cells to average

the 2
most right column's cells. For instance, in row 1, if
cells A1, A2, and A3 contained numbers 2, 33, 40
respectively, I need to have the formula skip looking

at
cells A4 through A10 and average the cells containing
numbers. Any ideas?
.

.


.

  #12  
Old May 4th, 2004, 06:44 PM
CindyA
external usenet poster
 
Posts: n/a
Default "Average If Not Blank" Formula

Thanks, I'll give it a try.


-----Original Message-----
On Tue, 4 May 2004 08:17:59 -0700, "CindyA"
wrote:

Here is a sample. Thanks for taking the time to look at
this.

A1=10
B1=8
C1=22
D1=4
E1=55
Cells F1 through J1 are blank.
In cell K1 I want the formula to look left, determine
which are the last TWO cells to contain numbers and
average just those two cells. So in this example, only
Cells D1 and E1 would be considered. Cells A1, B1, C1,

as
well as F1 through J1 would be ignored.


This *array-entered* formula will do what you want, I

think:

=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE(ISNUMBER(A1:J 1)

*COLUMN(A1:J1),{1,2})-1)))

To *array-enter* a formula, after typing or pasting the

formula, hold down
ctrlshift while hitting enter. Excel will place

braces {...} around the
formula if you did this correctly.

You can array-enter the formula in K1, and then drag it

down as many rows as
you have.


--ron
.

 




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 09:22 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.