-2

I want to extract some part of array. Last six elements and first three. How can I do that in Excel and OpenOffice Calc?

  1. Array that I have: 04:52 08:48 04:59 08:48 05:50 00:00 00:00

  2. I need #1: 08:48 04:59 08:48 05:50 00:00 00:00

  3. I need #2: 04:52 08:48 04:59

Copy of part of a comment:

What I want is to count sum of 7 (AC75 shows it) of AB63:AH63 last elements (don't make it serious that it has 7 elements at all, it will have different numbers):

SO28433287 question example

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3082220
  • 169
  • 1
  • 2
  • 6
  • 1
    please check [Excel vocabulary to find solutions faster](http://superuser.com/q/860919/118860). where does the array come from, what array formula creates it and why can't it return a different array, the one you need? – Aprillion Feb 10 '15 at 20:16
  • possible duplicate of [How do you extract a subarray from an array in a worksheet function?](http://stackoverflow.com/q/7815124/1176601) – Aprillion Feb 10 '15 at 20:18

1 Answers1

0

This may be easy in Excel because you have specified the positions of the elements you require. For example in ColumnB and copied across to ColumnG: 

=CHOOSE(COLUMN(),"04:52","08:48","04:59","08:48","05:50","00:00","00:00")

and in ColumnA and copied across to ColumnC.

Alternatively, if your array is in A1:A7, in B1: 

=IF(ROW()>1,$A1,"")

and in C1: 

=IF(ROW()<4,$A1,"")

both copied down and formatted to suit.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Couldn't figure it out.. All variants shows Err, maybe it's my fault. Here is my table for clearly view. What I want is to count sum of 7 (AC75 shows it) of AB63:AH63 last elements (don't make it serious that it has 7 elements at all, it will have different numbers). https://lh5.googleusercontent.com/-htWvWj3LItA/VNsku4j8MLI/AAAAAAAAAww/RHaBitJMg3w/w1244-h482-no/time.PNG – user3082220 Feb 11 '15 at 10:33
  • Ok. The table consists of days of week (horisontally) and 8 different weeks. The AC75 is the number of holiday days. So for example I want to take 12 days of holiday from 1st week of tuesday (means "antradienis" in the title. Then I should count sum of these cells: AC63:AH63 and AB64:AG64. – user3082220 Feb 12 '15 at 08:41
  • So I wanted to make other table with sums (from the day you take holidays using AC75 number and from the second table choose minimal sum. That's why I need to take from array last elements, cause then I'm taking holidays from tuesday, it means that it's left 6 days of first week (6 last elements) and 6 from second week (6 fist elements of second array). If AC75 is 20 then I neew to take 6 last elements from 1st week, 7 first from 2nd, and 7 from 3rd – user3082220 Feb 12 '15 at 08:49