Calculate 5 out of 6 columns

  • Thread starter Thread starter sonar
  • Start date Start date
S

sonar

Hi

What function can I use to instruct to calculate the first "used"
columns out of 7. It could be at random. eg. 1st, 3rd, 4th, 5th an
6th or 1st, 2nd, 4th, 5ht and 6th etc.

The columns are as follows:
(J14-K14); (Q14-R14); (X14-Y14); (AE14-AF14); (AL14-AM14); (AS14-AT14)

Help will be most appreciated.
'(e-mail address removed)' ([email protected]
 
Please clarify.

I see 6 sets of parentheses and
12 columns mentioned J,K,Q,R etc.
Can you give an example?
 
Hi
but what columns do you use for this?
- are they in adjacent columns or also scattered across your
spreadsheet
- what do you want to calculate? The sum, the average, etc
 
I am working on a staff register.

It has 7 sections: Monday, Tuesday, Wednesday, Thursday, Friday
Saturday, Sonday.

Each section has 4 columns namely: Start Time, End Time, Total Time
Overtime.

(J14-K14); Monday (Total Time - Overtime)
(Q14-R14); Tuesday (Total Time - Overtime)
(X14-Y14); Wednesday (Total Time - Overtime)
(AE14-AF14); Thursday (Total Time - Overtime)
(AL14-AM14); Friday (Total Time - Overtime)
(AS14-AT14) Saturday (Total Time - Overtime)

I am excluding Sunday at this time.

What I need is a formula that can look at the above cells, and assitai
which of these has hours in and take the first 5 that does, and ignor
the rest, and also make sure that the hours in that first 5 does no
exceed 28hrs.

Is this possible
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER) for
summing your total time for the first five values (col. J, Q, X, etc.)
=SUM(N(OFFSET($A$1,0,SMALL(IF(ISNUMBER(J14:AS14)*(MOD(COLUMN(J14:AS14)-
3,7)=0),COLUMN(J14:AS14)),{1;2;3;4;5})-1,1,1)))

If you also want to make sure that this sum does not excee 28 hours
(and you have stored your values as time values) use the following
array formula:
=MIN(SUM(N(OFFSET($A$1,0,SMALL(IF(ISNUMBER(J14:AS14)*(MOD(COLUMN(J14:AS
14)-3,7)=0),COLUMN(J14:AS14)),{1;2;3;4;5})-1,1,1))),28/24)
 
How would I use it for the following cells:

L14; T14; AB14; AJ14; AR14; AZ14; BH14

that is if I include my Sunday, I have these columns that is the sum o
(total - overtime)

Can it still be done
 
Hi
if you also want the first 5 values try the array formula
=SUM(N(OFFSET($A$1,0,SMALL(IF(ISNUMBER(L14:BH14)*(MOD(COLUMN(L14:BH14)-
4,8)=0),COLUMN(L14:BH14)),{1;2;3;4;5})-1,1,1)))
 
Back
Top