delete cells after calculation of average

[maartenroels]

Hi,

I`m working on a compression of a picture. For most of you that probably is pea-nuts, but I really just stopped wearing diapers when it comes to excel. At this point I have a sheet in front of me with 300 rows (length of the picture) and 185 columns (breadth of the picture). The cells are pixels and contain values ranging from 0 to 255.

I now need to do an operation I simply can't figure out. I need to select four cells at once (A1:A2 and B1:B2) and replace A1 by the average of these cells and the other three cells should be emptied during that operation. This operation I should do for the whole sheet. In the end I should see a chart with values in every two cells (A1, A3, A5 ... for the first row) as the other cells will be emptied ...

Does anybody know how a function that would do the job in excel (2003)?

Many thanks in advance!

Maarten

 

[VoG]

I'm no formula guru but I'm pretty sure that this is not possible with a formula.

For a start if in A1 you enter =AVERAGE(A1:B2) you will get a circular reference - OK, you can get around that by Tools > Options > Calculations tab and set Iterations to 1. But a formula can't delete the values in other cells.

 

[Weaver]

Hi VoG,

I'm very impressed! In what short time you got this solution. I also tried with basic functions and got about the same results, but I can`t run it in the same worksheet.

The basic problem seems to be that if I select four cells and write a function (average=A1:B2 e.g.) in the first cell (A1) excel no longer reads the value that was in that cell (255 in this case) so that I only get the average of the other three. How to make the arguments disappear after the average calculation, remains a mystery to me.

M.

Unfortunately, that's why you have to use a macro.

Has your tutor said it can be done with basic functions?

I guess you could do it if you wrote your values to another sheet.

How about if you create another sheet, in A1, you put

=IF(AND(MOD(ROW(),2)=1,MOD(COLUMN(),2)=1),AVERAGE(Sheet1!A1,B2),"")

Drag-copy this to an equal number of cells to your original data pattern

might not work all that well for odd nos of rows/columns tho

 

[Weaver]

Hi ExcelR8R,

I ran your command too, but it seems to contain an error in the 4th line. But many thanks nevertheless!

Maarten

forgot to mention you'd need to change the text between the quotes to the name of your sheet with the data. My bad!