average last 17 cells (variable)

  • Thread starter Thread starter brian thompson3001 via OfficeKB.com
  • Start date Start date
B

brian thompson3001 via OfficeKB.com

using formula in A2 =average(IT:Index(A1:IT1,sumproduct(large(column(A:IT)*
(A1:IT1<>""),A4))))
A4 is variable cell for calculation. However, I require result to be in row
1. Keep getting curcular ref. Require result in B3

Can anyone assist?

brian(at )thompson3001.fsnet.co.uk
Regards

Brian
 
A2 is within the range A:IT, and would therefore produce a circular
reference. It looks like you might be able to use COLUMN(A1:AT1) instead.
If that doesn't solve the problem, try copying the formula from the formula
bar and paste that into your post, instead of retyping the formula. As
posted, the formula gives a #NAME? instead of a circular reference, because
of the IT:Index...

Jerry
 
Sorry Jerry, think I gave a bum steer on my first posting. I did get this
formula off the net. and it was for column average of last numbers, I
attempted to convert into row. My result is required in the same row as the
last numbered cell. Have tried sugestion but no joy.

Regards

Brian
 
Copying off the net is not the same as copying from Excel. You either need
to describe what you expect the cell to do, or point to the web address where
you got the formula, because it is not clear what you are up to, and the
posted formula does not work and does not give the error that you report.

Jerry
 
Jerry said:
Copying off the net is not the same as copying from Excel. You either need
to describe what you expect the cell to do, or point to the web address where
you got the formula, because it is not clear what you are up to, and the
posted formula does not work and does not give the error that you report.

Jerry
Sorry Jerry, think I gave a bum steer on my first posting. I did get this
formula off the net. and it was for column average of last numbers, I
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...

Thanks Jerry for being so patient

I have to average the last n cells in a row. I have already go the
spreadsheet designed and the result is required in C3. All the data ifor the
averages is to right of this cell, D3,D4 D5 etc. I input new data each week
in the next blank cell. I would prefer it if cell A1 would hold the number
for the adjustment

Hope I have explained it ok

Regards

Brian
 
Jerry said:
Copying off the net is not the same as copying from Excel. You either need
to describe what you expect the cell to do, or point to the web address where
you got the formula, because it is not clear what you are up to, and the
posted formula does not work and does not give the error that you report.

Jerry
Sorry Jerry, think I gave a bum steer on my first posting. I did get this
formula off the net. and it was for column average of last numbers, I
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...

Thanks Jerry for being so patient

I have to average the last n cells in a row. I have already go the
spreadsheet designed and the result is required in C3. All the data ifor the
averages is to right of this cell, D3,D4 D5 etc. I input new data each week
in the next blank cell. I would prefer it if cell A1 would hold the number
for the adjustment

Hope I have explained it ok

Regards

Brian.
 
I am confused. D3,D4,D5 etc refer to cells in a column (D), not a row. Your
original formula seemed primarily concerned with row 1 and seemed to be
trying to put the result in A2, not C3. What is in A4 that the original
formula used? What adjustment are you performing with A1? Your subject
asked about averaging 17 cells, but now you mention averaging n cells; where
is n obtained?

Jerry

Brian Thompson via OfficeKB.com said:
Jerry said:
Copying off the net is not the same as copying from Excel. You either need
to describe what you expect the cell to do, or point to the web address where
you got the formula, because it is not clear what you are up to, and the
posted formula does not work and does not give the error that you report.

Jerry
Sorry Jerry, think I gave a bum steer on my first posting. I did get this
formula off the net. and it was for column average of last numbers, I
Click to expand...
[quoted text clipped - 16 lines]
Brian
Click to expand...
Click to expand...

Thanks Jerry for being so patient

I have to average the last n cells in a row. I have already go the
spreadsheet designed and the result is required in C3. All the data ifor the
averages is to right of this cell, D3,D4 D5 etc. I input new data each week
in the next blank cell. I would prefer it if cell A1 would hold the number
for the adjustment

Hope I have explained it ok

Regards

Brian
Click to expand...
 
Jerry said:
I am confused. D3,D4,D5 etc refer to cells in a column (D), not a row. Your
original formula seemed primarily concerned with row 1 and seemed to be
trying to put the result in A2, not C3. What is in A4 that the original
formula used? What adjustment are you performing with A1? Your subject
asked about averaging 17 cells, but now you mention averaging n cells; where
is n obtained?

Jerry
[quoted text clipped - 22 lines]
Click to expand...

Jerry

Hit me on the head and call me stupid ! I got a little flustered when typing

I am working out the average hrs of employees for the WTD (Working time
directive). Currently attempting to work out the last 17 weeks, however,
later this may turn to be 26 weeks if "Opt out" is agreed.

Cell A1 = number of last weeks to average, ignoring blanks cells
Cell A3 = Name
Cell B3 = Result of last 17 weeks
Cell C3 = Hrs
Cell D3 = Hrs
Cell E3 = Hrs
Etc, Etc

If it is not possible to enter a variable in A1, then I will go with the
fixed 17 weeks

Hope this is clearer?

Regards

Brian


 
Try...

=AVERAGE(INDEX(C3:IV3,LARGE(IF(C3:IV3<>"",COLUMN(C3:IV3)-COLUMN(C3)+1),A1
)):INDEX(C3:IV3,MATCH(9.99999999999999E+307,C3:IV3)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Much clearer, thanks. Domenic has given one possible formula; here is another

=AVERAGE(IF((COLUMN(C3:IV3)>=LARGE(IF(ISNUMBER(C3:IV3),COLUMN(C3:IV3)),A1))*ISNUMBER(C3:IV3),C3:IV3))

both must be array entered (Ctrl-Shift-Enter).

The two formulas will give different results if there is non-numeric data
among the numeric data. Dominic's formula will average the last cell that
has anything in it (other than a zero length string) together with the
preceding A1-1 non-empty cells, whether numeric or not. My formula will
average the last A1 numeric values, even if it has to skip over some cells to
find A1 numeric values.

Your call which approach is more in keeping with your needs.

Jerry

:
....
 
Excellent it works !

thank you

Regards
Brian
Much clearer, thanks. Domenic has given one possible formula; here is another

=AVERAGE(IF((COLUMN(C3:IV3)>=LARGE(IF(ISNUMBER(C3:IV3),COLUMN(C3:IV3)),A1))*ISNUMBER(C3:IV3),C3:IV3))

both must be array entered (Ctrl-Shift-Enter).

The two formulas will give different results if there is non-numeric data
among the numeric data. Dominic's formula will average the last cell that
has anything in it (other than a zero length string) together with the
preceding A1-1 non-empty cells, whether numeric or not. My formula will
average the last A1 numeric values, even if it has to skip over some cells to
find A1 numeric values.

Your call which approach is more in keeping with your needs.

Jerry

...
Hit me on the head and call me stupid ! I got a little flustered when typing
Click to expand...
[quoted text clipped - 14 lines]
Hope this is clearer?
Click to expand...
Click to expand...
 
Back
Top