Who's good with Excel?

Phate

Phate

Phate

Caporegime
Joined
1 Nov 2003
Posts
35,691
Location
Lisbon, Portugal
My Excel Formula abilities are...rubbish..

What I'm trying to do is

In Cells B4 - AE4 there are a series of 1's and 2's and some of the cells are blank

I need it so in cell AG4 and AH4 it totals the number of 1's and 2's

for example if its set out as below:

1 - 1 - 1 - 2 - 1 - 2 - 2 - Blank - Blank - 2 - 1 I want to display in AG4 the total number of 1's being 5 and in AH4 the total number of 2's being 4

Can anyone help please? :)

Ta,
Jake
 
Use a countif formula to do it, for example:

=COUNTIF(B4:AE4,1) would display the number of 1s and =COUNTIF(B4:AE4,2) would display the number of 2s.
 
How about a formula to calculate the amount of current redundancy that would be due, can anyone have a shot at that?

The columns I have are :-

Name : DOB : Date Started : Weekly Pay

This is relative to 'Todays Date' - so will automatically update as time goes on.

The criteria for Statutory redundancy pay are :

how long you have been continuously employed
your age
your weekly pay, up to a certain limit (£430 current maximum)

You will get:

0.5 week’s pay for each full year of service where your age was under 22
1 week’s pay for each full year of service where your age was 22 or above, but under 41
1.5 week’s pay for each full year of service where your age was 41 or above

I have created a spreadsheet, but it is full of manually adjusted formula's as I struggled with the variables around age / length of service.

I get the feeling this isn't an easy one to fully automate :)
 
You need to clarify what happens the year that the person changes from 21-22.

So the year that you turn 22 does that count as 0.5 weeks pay or 1 weeks pay? or is it pro-rata?
 
feedtheram said:
You need to clarify what happens the year that the person changes from 21-22.

So the year that you turn 22 does that count as 0.5 weeks pay or 1 weeks pay? or is it pro-rata?
Click to expand...

Take years as integers and you only get redundancy on full years service. So if at the end of the 3rd year of service you had just turned 22 then I assume you would get 2 yrs at 0.5 weeks pay 1 yr at 1 weeks pay. So it's absolute at that specific point, not pro-rata.

Remember you get nothing for the first 2 years, but then those 2 years are included in the calculation after you reach that point.
 
Freakbro said:
How about a formula to calculate the amount of current redundancy that would be due, can anyone have a shot at that?

The columns I have are :-

Name : DOB : Date Started : Weekly Pay

This is relative to 'Todays Date' - so will automatically update as time goes on.

The criteria for Statutory redundancy pay are :

how long you have been continuously employed
your age
your weekly pay, up to a certain limit (£430 current maximum)

You will get:

0.5 week’s pay for each full year of service where your age was under 22
1 week’s pay for each full year of service where your age was 22 or above, but under 41
1.5 week’s pay for each full year of service where your age was 41 or above

I have created a spreadsheet, but it is full of manually adjusted formula's as I struggled with the variables around age / length of service.

I get the feeling this isn't an easy one to fully automate :)
Click to expand...


Assuming A: Name, B: DOB, C: Start, D: Pay at a very basic level:

=IF((TODAY()-B2)/365<22,FLOOR((TODAY()-C2)/365,1)*D2*0.5,IF((TODAY()-B2)/365<41,FLOOR((TODAY()-C2)/365,1)*D2,FLOOR((TODAY()-C2)/365,1)*D2*1.5))

The formula to calculate the pay needs adjusting slightly as it only works out as if you were that age permanently whilst having worked. I may update it in a bit after I've got some lunch.
 
You must log in or register to reply here.
Back
Top Bottom