Creating a colum consisting of interval averages of an input column

Hi,

I am new to this forum and new to vba coding.

I hope i am posting this in the correct part of the forum, if not please inform me.

My problem is the following:

I have a column of data of 40 values long. I wish to create a new column of data where the first value in the new column consists of the average value from the first 5 values in the original column. The second value in the new column is the average of the next 5 values in the original column. The third value in the new column is the average of the next 5 values in the original column, and so on. Therefore my new column will consist of 8 values. (40/5= 8)

I will then look to expand on this work by allowing a user to input a "whole number" value, where this value will dictate how many values will be used to calculate the average. For example if the user input 2, the new column would be 20 values long, and each value will consist of the average between 2 values in the original column.


Any help would be greatly apreciated.

Cheers,

Try this:-
List in column "A", Divisor in "B1", results column "C".

Please Login or Register  to view this content.

Regards Mick

Hey Mick,

That worked perfectly. Thanks for your help mate. Nice one

Thanks for the feedback
Regards Mick

Perhaps, Either the formula or the Actual values

Please Login or Register  to view this content.

Hey,

Once again, thanks very much

Works a treat.

whats the purpose of the (&) symbol within the code

Cells(c, "O") = "=SQRT((" & Cells(c, "M") & "^2)+(" & Cells(c, "N") & "^2))"

is that just the syntax of the VBA code?

The formula part is really a string and the cell parts are really variables and in order to join a string and a variable you use a "&" ampersand.
Mick

Hey MickG

I have another question, and since you have been very good in the past im wandering if you wouldnt mind helping me out yet again

Here it is..........

First of all, whats the difference between a combo-box and a list-box?

Second : I want to use one of the above to contain several options, A,B,C,D,E. If the user selects either A,B,C,D,E i would like VBA to populate a cell ( say A1) with a defined value, (this value is different for A,B,C,D,E).

Cheers mate

wetfish

A combobox is really a drop down list to select from , and a list box is a complete list to select from.
Below are 2 bits of code for a combobox. If you right click your sheet tab and select "view code", the Vb window appears and you can paste both codes into that window.
The first code "Worksheet_Activate" loads the combobox with your letters and the second code "Combobox1_Change" has a select case piece of code, so on selection of a particular letter, cel "A1" will fill with whatever you place in the code, (1 to 4 at the momernt) Change to suit.
Nb:- When you have pasted the code you will need to reactivate the sheet to load the Combobox.

Please Login or Register  to view this content.

Regards Mick

Hey,

Thanks again Mick, greatly apreciated

Enjoy your day,

regards, baz

Hey Mick,

Im trying to write a simple bit of code that that fills a cell [blank] if something is 1 and fills a cell with a value if something is zero. However, i would also like to change the colour of the cell depending upon whether something is 1, and 0. If something is 1 i want to have a blank green cell, and if something is 0 then i want to have a red cell with a value in it.

The code i have so far is : =IF(Calculations!AP17=0, Calculations!AI17,"")

The code above works, i just want to edit the code to also colour the cell red if zero and green if 1

in basic terms, i want something like this :
=IF(Calculations!AP17=0, Calculations!AI17 [and red],"" [and green])

i hope ive explained this ok.

Your help would be greatly apreciated,

Cheers,

WF

Have a look here !!!
http://www.contextures.com/xlCondFormat01.html

Hey Mick,

Come across a new problem.

At the moment i have a pice of code that carries out several calculations using variables H1,H2,H3,...,H10. At the moment i have hard coded these variables into teh sub and would like to edit my code to make use of a user form.

I basically want to insert a user form within the main body of a sub without ending that sub. The user form asks users for to enter values H1,H2,H3,...,H10. These values of H1,H2,H3,...,H10 are then passed into the sub and used in calculations


I hope the question is clear.

Any help would be greatly apreciated,

Thanks in advance

WF