| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding first three and last three entries in rows of data
Thanks
That works as well Regards -- Rich "Domenic" wrote: Here's another way, without using the volatile function OFFSET... For the first three... =IF(COUNT(A2:K2)=6,SUM(A2:INDEX(A2:K2,SMALL(IF(A2 :K2<"",COLUMN(A2:K2)-C OLUMN(A2)+1),3))),"Invalid") For the last three... =IF(COUNT(A2:K2)=6,SUM(INDEX(A2:K2,LARGE(IF(A2:K2 <"",COLUMN(A2:K2)-COLU MN(A2)+1),3)):K2),"Invalid") Note that these functions need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Rich wrote: I have multiple rows of data where I want to add the first three entries in each row and compare the total to the last three entries in the row. However some of the rows have blank fields so some rows will have differently spaced first and last three entries. If there are less than six entries then the row is invalid Here is an example A B C D E F G H I J K 1 5 6 8 4 2 5 7 8 3 1 2 1st 3 = 19, last 3 = 6 2 - 3 4 2 - 4 4 - 8 9 3 1st 3 = 9, last 3 = 20 3 4 - - 8 7 - 5 6 - 2 - 1st 3= 19, last 3 = 13 4 - 1 2 4 - - - 7 2 - - invalid So I need a couple of fomulas that add the frist three and last three of any list of data |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summarize Data and automatically update including adding rows | Excel Discussion (Misc queries) | |||
| Adding data from multiple rows | Excel Discussion (Misc queries) | |||
| Adding data from multiple rows | Excel Worksheet Functions | |||
| Adding Consecutive Entries | Excel Discussion (Misc queries) | |||
| Adding Consecutive Entries | Excel Discussion (Misc queries) | |||
Hybrid Mode
