Home |
Search |
Today's Posts |
#1
|
|||
|
|||
average last 17 cells (variable)
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 -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
average last 17 cells (variable)
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 "brian thompson3001 via OfficeKB.com" wrote: 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 -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
average last 17 cells (variable)
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 brian thompson3001 wrote: 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 -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
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 "brian thompson3001 via OfficeKB.com" wrote: 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 brian thompson3001 wrote: 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 -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
Jerry W. Lewis wrote:
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 [quoted text clipped - 16 lines] Brian 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200511/1 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
Jerry W. Lewis wrote:
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 [quoted text clipped - 16 lines] Brian 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200511/1 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
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" wrote: Jerry W. Lewis wrote: 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 [quoted text clipped - 16 lines] Brian 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200511/1 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
Jerry W. Lewis wrote:
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 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 [quoted text clipped - 22 lines] 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 Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200511/1 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
Try...
=AVERAGE(INDEX(C3:IV3,LARGE(IF(C3:IV3<"",COLUMN(C 3: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! In article <5775c4b057cda@uwe, "Brian Thompson via OfficeKB.com" <u15682@uwe wrote: 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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
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 "Brian Thompson via OfficeKB.com" wrote: .... 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? |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
Excellent it works !
thank you Regards Brian Jerry W. Lewis wrote: 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 [quoted text clipped - 14 lines] Hope this is clearer? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200511/1 |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
average last 17 cells (variable)
You're welcome. Glad it helped.
Jerry "Brian Thompson via OfficeKB.com" wrote: Excellent it works ! thank you Regards Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
Remove variable text in cells | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel | |||
How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions |