Can Median() be put in an Array Formula?

[MarvinP]

Hi,

I solved this attached timecard problem a few days ago and now wonder if I could have rolled the formulas into an easier formula.

The given numbers are the number of hours an employee works per day, for a full week. They get normal pay for the first 8 hours, time and a half for the next two hours and double time for hours over 10 in a single day. I'd like to see if my formulas in the yellow can be reduced to an array formula (or any easier method).

[zbor]

Looks like it can't.
I've come closest with this:

Please Login or Register  to view this content.

It evaluate array but alway end with 1st number of it (B column)

[gmr4evr1]

Question, why are you using median?

[MarvinP]

I was thinking that the Array Constant of {1,2,3,4,5,6,7} could somehow be used to array the values inside the Median formula.

[MarvinP]

Hey Tom,

Good question. I saw one of the smart Guru's use Median a few months back and needed to really study to see why.

Median mean the middle number. In the normal time a person could work 5 hours so between 0 and 8, 5 is what they get paid normal time for. Now if they worked 9 hours then the middle between 0,8 and 9 is 8! They only get paid normal time for the first 8 hours.

I hope that makes sense, but it sure didn't when I first saw it.

[gmr4evr1]

OK, now it does, but, I noticed the totals don't seem to add up correctly.

[zbor]

MarvinP you are right.
Median is the numerical value separating the higher half of a data sample from the lower half.

But in case of 3 numbers it's always middle one so it's shorter way to pick middle values.

I use it mostly with dates or times. For example to check does some date fit between two dates.

[MarvinP]

The totals are on a daily basis. When I first did this problem I added all the hours for a week and was wrong. The OP wanted to pay based on daily hours.

In my Example in Row 2, Shawn worked 6 hrs one day and 11 the next day. He was paid 6 and 8 = 14 hours at normal time. Because time and a half is between 8 and 10 hours, he got 2 hours at time and a half. Because double time is any time worked over 10 hours in a day, he got the last hour at double time.

Does it make sense now? Change some numbers in my sample to see if my formulas work correctly.

[gmr4evr1]

Sorry...I may not have a grasp of what your are doing. Are you only doing toe totals for one of the days worked, or all?

[gmr4evr1]

Wait wait, I got it now, I see how it all works. BTW, I want to work for whatever company this is if that is how they do their pay.

[daddylonglegs]

Hello Marvin,

I think you could do this without using MEDIAN

For totals try this array formula

=SUM(IF(B2:H2>8,8,B2:H2))

then for Double Time

=SUM(IF(B2:H2>10,B2:H2-10))

Now the Overtime figure is just all the hours except those 2 columns so

=SUM(B2:H2)-I2-K2

[MarvinP]

GREAT Answer if there are only 3 pay rates.

What if there were 4 rates?

Normal Time = 0 to 8 hrs
Extra Time = 8 to 10 hrs
Better Time = 10 to 12 hrs
Double Time = 12 or more hours.

Sorry I changed the problem as your answer was GREAT!

I find many of these answers are simply how you look at the problem. DLL is still my hero. (reped)

[daddylonglegs]

Hey Marvin, no problem

If you want to calculate 8 to 10 explicitly then you can use a similar formula but with an extra IF, e.g.

=SUM(IF(B2:H2>8,IF(B2:H2-8>2,2,B2:H2-8)))

Where the 2, of course is derived from 10-8

Of course you can do the same for 10-12 or any other range