Calculate 5 out of 6 columns

[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]

 

[Leo Heuser]

Please clarify.

I see 6 sets of parentheses and
12 columns mentioned J,K,Q,R etc.
Can you give an example?

 

[sonar]

The columns your seing is "total time" less "overtime" which = my norma
time

 

[Frank Kabel]

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

 

[sonar]

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

 

[Frank Kabel]

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)

 

[sonar]

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

 

[Frank Kabel]

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)))

 

[sonar]

Hi

I have tried your formula, dont understand it, so I couldnt modify it.

I am including a file for you to see, hope it helps to understand wha
I am trying to do

Attachment filename: sonar-file.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52476

 

[Frank Kabel]

Hi
and if you now could explain what you want to calculate based on your
example

 

[sonar]

I still have the same problem. I have tried your formula, but I think
did something wrong. You were saying about using the CTRL+SHIFT+ENTER
Should I do that with the cell highlighted with the mouse befor
entering the formula?

As you can see on the example:

The cells: L14; T14; AB14; AJ14; AR14; AZ14; BH14

are the outcome of two columns, just to make it easier for me at thi
time. the total hours less overtime.

What the boss wants is a little complicated.

firstly, overtime is calculated on a daily basis, hence the overtim
columns for each day. Then monthly if the person works more than 40hr
per week.

A person gets 28hrs base, and can beflexed up to 40hrs per week at th
same rate, this is called additional time.

The person can not get more than 12hrs additional (40-28), but at th
same token, the person can not work more than 5 days, in which case th
person could work 5hrs per day, which means he worked 25hrs, and th
rest is overtime, even though he has not worked his full 28hrs.

I have included a total column at the back, with daily overtime and
monthly overtime, with a column overtime taking the greater of the two
Base hours will always be constant as people with a base 28 will neve
get less or more, unless unpaid, but that’s not be worried about.

I am taking it a step at the time in building it up. Trying hard t
make it "child" and "idiot" proof, as the people who use them, are no
excel experienced enough.

* If I can include a formula that will look at the cells L14; T14
AB14; AJ14; AR14; AZ14; BH14 and ascertain which first 5 has hours
then I can place another column to see take the total less the hour
generated from the formula can give me the overtime.

Its a little complicated as you can see, I am trying to keep it simple
But its not going to be that simple unfortunately

 

[Frank Kabel]

Hi
normally you would finish your formula entry with hitting ENTER. Just
hit CTRL+SHIFT+ENTER together instead of a single ENTER

 

[sonar]

Thanks, it worked

 

[sonar]

Ok, now the formula is accepted, but its not calculating. did I still d
something wrong

Attachment filename: sonar-file.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52488