Excel Spreadsheet

[Newfie809]

Hi

My last posting I had asked for help and when I used the formula they gave
me, I had said that it did not work. I did send another message to say that
I was wrong and that it did work.

I would like somemore help if anyone can help me with this one.

It's the same spreadsheet and I could use some more input. Thanks
A B C D E F
1. Name Grid Years FTE Amount Allowance
2. John A1_E 10.0 1.0 00.00 00.00
3. Jane A2_E 9.5 .50 00.00 00.00
4. Judy A3_E 9.7 .75 00.00 00.00
5. Bill A3_E 9.7 1.0 00.00 00.00

I have to round down so if I have someone in A3_E and the years are 9.7 it
should be 1.75 at 9 years service.

Can anyone help.

thanks




Newfie

 

[Bob Phillips]

=INT(C4)*1.75

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Newfie809]

Thanks Bob, but I did not explain myself very clearly.
I would like to create a formula that would add the
FTE data and put it into another spreadsheet.

There are 1300 employees on the Salary Grid and say I have
40 employees at A1_E 1 year and 46 employees at A1_E at
1.2 years. I have to combine them to read as 86 employee
A1_E at 1 year, the formula I am using now is
=sumproduct(--(B3:B1300="A1_E"),--(C3:C1300=1)*(D31300),
So what I am looking for is where to put the greater than 1 but less than 2.
I tried =sumproduct(--(B3:B1300="A1_E"), --(C3:C1300>=1,<=2)*(D31300),
but it not working for me.

Thanks again for your help.

 

[Bob Phillips]

You certainly didn't. I would never have come anywhere near that with your
originaldescriptin.

=sumproduct(--(B3:B1300="A1_E"),--(C3:C1300>=1)"),--(C3:C1300<2),D31300)

or even

=sumproduct(--(B3:B1300="A1_E"),--(ROUNDDOWN(C3:C1300,0)=1),D31300)



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Newfie809]

So sorry for the way I explained myself. But thank you very much for your
response.

thanks again.

 

[Bob Phillips]

No problem, makes for an interesting ride. Are you sorted now?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Newfie809]

Yes thank you.

But I will have more questions in the future.

thanks again