Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
spreadsheet formula
Good morning,
i have a manager who wants to average a set of cells. he there are indefinate number of cells in a row and he wants to average only the current entry plus only the last three consecitive entries. example: if a1,b1,c1,d1,f1 all have entries he wants the average based on the info in b1,c1,d1,f1. when e1 has data entered he wants the average to be based on c1,d1,f1,e1. there are 20+ rows that need to have this function. can someone help, this is beyond my capabilities. thanks doug |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
spreadsheet formula
Doug,
This will total it for row 1 =SUM(N(OFFSET(A1,,LARGE((1:1<"")*(COLUMN(INDIRECT ("A:IV"))),{1,2,3,4})-1,1, 1))) it is an array formula, so commit with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "doug" wrote in message ... Good morning, i have a manager who wants to average a set of cells. he there are indefinate number of cells in a row and he wants to average only the current entry plus only the last three consecitive entries. example: if a1,b1,c1,d1,f1 all have entries he wants the average based on the info in b1,c1,d1,f1. when e1 has data entered he wants the average to be based on c1,d1,f1,e1. there are 20+ rows that need to have this function. can someone help, this is beyond my capabilities. thanks doug |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
spreadsheet formula
Thanks Bob,
i must be doing something wrong though. it works on a blank worksheet but when i put it into e8 to calulate starting at F8 + it gives me a #N/A error. i changed the formula to look as follows =SUM(N(OFFSET(F8,,LARGE((1:1<"")*(COLUMN(INDIRECT ("F:IV"))),{1,2,3,4})-1,1,1))) the formula is in cell E8 thanks doug "Bob Phillips" wrote: Doug, This will total it for row 1 =SUM(N(OFFSET(A1,,LARGE((1:1<"")*(COLUMN(INDIRECT ("A:IV"))),{1,2,3,4})-1,1, 1))) it is an array formula, so commit with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "doug" wrote in message ... Good morning, i have a manager who wants to average a set of cells. he there are indefinate number of cells in a row and he wants to average only the current entry plus only the last three consecitive entries. example: if a1,b1,c1,d1,f1 all have entries he wants the average based on the info in b1,c1,d1,f1. when e1 has data entered he wants the average to be based on c1,d1,f1,e1. there are 20+ rows that need to have this function. can someone help, this is beyond my capabilities. thanks doug |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
spreadsheet formula
Try changing to =AVERAGE(N(OFFSET(A8,,LARGE((F8:IV8<"")*(COLUMN(I NDIRECT("F:IV"))),{1,2,3,4})-1,1,1))) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=500152 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
spreadsheet formula
Thanks, that did the trick
"daddylonglegs" wrote: Try changing to =AVERAGE(N(OFFSET(A8,,LARGE((F8:IV8<"")*(COLUMN(I NDIRECT("F:IV"))),{1,2,3,4})-1,1,1))) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=500152 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do i turn the spreadsheet into a formula page? | New Users to Excel | |||
Linking formula to external spreadsheet | Excel Discussion (Misc queries) |