average in increments that aren't integers

[jacob128]

Hi,

I need to average every 7.11111 cells in column A (from row 1 to row 192), then column b seperately and so on... (to column QB) (ie rounding to the nearest whole number of cells, so most will average the contents of 7 cells, but some will average the values of 8 cells)

I also need to average every 16.4444 cells in row 1, (which has the same range as above) but i guess if i transpose my data i can use the same formula, just changing the number by which it averages.

i am useless when it comes to excel formulae, so could someone please show me how to write a formula that averages a number of cells incrementally without the increments being whole numbers?

Thanks in advance

Jacob

 

[T. Valko]

Hi,

I need to average every 7.11111 cells in column A (from row 1 to row 192), then column b seperately and so on... (to column QB) (ie rounding to the nearest whole number of cells, so most will average the contents of 7 cells, but some will average the values of 8 cells)

I also need to average every 16.4444 cells in row 1, (which has the same range as above) but i guess if i transpose my data i can use the same formula, just changing the number by which it averages.

i am useless when it comes to excel formulae, so could someone please show me how to write a formula that averages a number of cells incrementally without the increments being whole numbers?

Thanks in advance

Jacob

Both 7.1 and 16.4 will get rounded down to 7 and 16.

What is the significance of 7.1 and 16.4?

 

[nodroj81]

Would your cells all have equal weight? For example, if you have the following values

A1: 2
A2: 3
A3: 4
A4: 5
A5: 6
A6: 7
A7: 8
A8: 9

and equal weight would mean each cell should be multiplied by =7.1111 / 8 cells = 0.88889

With this method your average would = 4.8888

Or using the example above, will cells A1:A7 have a weight of 1, and A8 only have 0.11111 weight? With this method your average would = 5.062499.

Are either of these on track with what you're looking for?

 

[BenMiller]

Hi,

I need to average every 7.11111 cells in column A (from row 1 to row 192), then column b seperately and so on... (to column QB) (ie rounding to the nearest whole number of cells, so most will average the contents of 7 cells, but some will average the values of 8 cells)

I also need to average every 16.4444 cells in row 1, (which has the same range as above) but i guess if i transpose my data i can use the same formula, just changing the number by which it averages.

i am useless when it comes to excel formulae, so could someone please show me how to write a formula that averages a number of cells incrementally without the increments being whole numbers?

Thanks in advance

Jacob

Do you always want to round down? Or regular rounding? I.E. will 35.55555 be 35 or 36?

 

[jacob128]

I have one dataset with 444*192 cells, and another with 27*20 cells, and i need to average the larger one so that it is rescaled to the same size of the smaller one (ie 16.44 x and 7.1 x) accurately, and integers are not accurate enough for the work I am doing. Is there a way to do this?

 

[jacob128]

@nodroj81 they would all have equal weighting and @benmiller just regular rounding will be fine, thanks

 

[BenMiller]

If I understand what you're trying to do, her could be a solution. A bit complicated and not so smooth, but tell me if this helps.
Let's say your data is in column A, starting from cell A1. Add two columns to the right of your data. These are the new B, and C.
In B1, enter 7.11111. In B2, enter =B1+7.1111. Drag that formula down enough cells to cover one seventh of your data. (If you have seventy cells, for example, make sure you drag this at least 10 cells down).
Now in cell C1, enter =INDIRECT(ADDRESS((ROUND(B1,0)),1)) and drag down until it starts saying 0. On the bottom of that, do the AVERAGE function and select that range.
Should give you what you're looking for.
Let me know if this is what you need!

 

[BenMiller]

Whoops! I misunderstood your question. I gave you a way to average the 7th, 14th, 21st, 28th, 36th, etc. cells. I got it now. Give me a few minutes; I'll have something for you.

 

[jacob128]

yes, thanks, i was just about to say that! haha thank you

 

[BenMiller]

Ok here's a solution. It's messier but it works. (If I was better at array formulas I could probably do better but bear with me).
To the top of your data, insert a blank row. To the right of your data, insert four new columns.
Now your data starts at A2, because of the blank row on top. In B2, enter =B1+7.11111 and drag it down, again to at least a seventh of the amount of rows you have.
In C2, enter =ADDRESS((ROUND(B1,0)+2),1) and drag it down as long as the data in column B. In D2, enter =ADDRESS((ROUND(B2,0)+1),1) and drag it down as well.
Now in E2, enter =AVERAGE(INDIRECT(C2):INDIRECT(D2)) and drag it dow.
BAM! There's your data. Copy/paste values or whatever, and do what you need to do with it.

Let me know if this works!