excel help

[pickamovie]

i want to do a column opration.in a coulmn some negative values occur
...i want to write a formula .
he is my column for example
1
2
4
-2
5
4
-7.
if i see negative number(-2), i want the average of (1,2,4)in the cell
adjacent to -2. when i reach the cell containing -7 i need the average
of (4,5) in the adjacent cell to -7.

i am very new to excel..
any help is appreciated.

 

[Sandy Mann]

With your data in column A from A1 down, enter the following formula in B2
and copy down using the fill handle:

=IF(A2<0,SUM(OFFSET(A2,-(ROW()-MAX(--($A$1:A1<0)*(ROW($A$1:A1))))+1,0):A1),"")

Note: This is an array formula so you have to enter the original formula by
presing and holding the Ctrl and Shift keys while you press Enter
--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[pickamovie]

hi sandy first of all thanks for the reply.
i tried copying the formula in the cell B2 and pressin
ctrl+shift+enter.
after doing this when i try to drag it to all of the cells it is givin
just the sum but not the average..
thank

 

[pickamovie]

hi sandy i just removed that SUM and replaced with AVERAGE now it is
working.
thank u very much .

 

[pickamovie]

hi,
using the formula u gave me i got the average in desired cells.and now
i want to copy this whole column to another column and i want to remove
the empty cells ...when tried pressing ie.goto

special>balnks>ok(normally after this step the empty cells are

selected and will be light blue in color.but when do this it says
either no cells slected or it is not showing any selected cells.)
and then edit>delete>shift column up or delete entire row.
but it did not work ..i dont know why.could u help on this?

thanks in advance

 

[Sandy Mann]

First of all my apologies for not noticing that you wanted AVERAGE() not
SUM(). By the time that I had worked out the formula I had forgotten what
it was that you asked.

If I understand you correctly the reason is that the *blank* cells are not
blank - just not showing any display. If you click into a *blank* cell you
will see that you still have the formula in the cell. By definition if a
cell contains a formula is cannot be blank.

Try doing an autofilter for non-blanks and copy that to a new location.

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[pickamovie]

hi sandy.
thank u very much for ur help.and coming to the *blank* cell, it is
correct what u said that the cell is not blank(contains formula).so i
did it using autofilter.

regards