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

 

[Weaver]

sounds like a job for a macro.

What would you do at the end, as there are an odd number of columns?

Gotta be honest, I'm quite interested as to how you got the picture info into excel in the first place!

 

[maartenroels]

sounds like a job for a macro.

What would you do at the end, as there are an odd number of columns?

Gotta be honest, I'm quite interested as to how you got the picture info into excel in the first place!

Hi,

I used matlab, where i split the picture in two so that the data fit in a worksheet in excel. in case of an odd number (good question by the way) i simply delete the most left column of part one of the picture data sheet and the most right column of part two.

I'm still breaking my head on this. Any idea?

M.

 

[VoG]

Try this with a copy of your sheet. Press ALT + F11 to open the visual Basic Editor, Insert > Module and paste in

Sub RplAvg()
Dim LC As Long, LR As Long, i As Long, j As Long, x As Variant, rng As Range
LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LR Step 2
    For j = 1 To LC Step 2
        Set rng = Range(Cells(i, j), Cells(i + 1, j + 1))
        x = WorksheetFunction.Average(rng)
        rng.ClearContents
        Cells(i, j).Value = x
    Next j
Next i
End Sub

Press ALT + F11 to return to your sheet, Tools > Macro > Macros, highlight RplAvg and click the Run button.

 

[Weaver]

snap...almost!

Sub fourAverage()
    Application.ScreenUpdating = False
    Dim rng As Range, r As Integer, c As Integer, currR As Integer, currC As Integer, a As Integer
    With Sheets("source")
        r = .Range("A1").CurrentRegion.Rows.Count
        c = .Range("A1").CurrentRegion.Columns.Count
        For currR = 1 To r Step 2
            For currC = 1 To c Step 2
                Set rng = .Range(.Cells(currR, currC), .Cells(currR + 1, currC + 1))
                a = Int(Application.WorksheetFunction.Average(rng))
                rng.ClearContents
                .Cells(currR, currC) = a
            Next currC
        Next currR
    End With
    Application.ScreenUpdating = True
End Sub

 

[Weaver]

...and what are you doing with the results?

 

[maartenroels]

That looks interesting! Thanks. I tried to avoid a macro as I wanted to push the basic functions to their limits. I'll surely try your suggestion and will let you know the result in a second. However, I keep on wondering whether basic functions may help me on this . Any idea?

Maarten

 

[maartenroels]

...and what are you doing with the results?

I'll hand them in to my tutor who is pushing us to learn all about excel without a manual ... What i'm learning in the first place is that forums are as interesting as courses here in uni!

Maarten

 

[maartenroels]

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.

 

[maartenroels]

Hi ExcelR8R,

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

Maarten