Whats the best way ?

  • Thread starter Thread starter MAS
  • Start date Start date
M

MAS

I am happy to put in the work to get my head around visual basic 'Again' if
I have too but if I can do the following with formulas so much the better.

In brief I have the following table

Name Date Years Leave
John 01/08/01 2
Fred 22/05/89 14


So you see John started with the company on 01/08/01 and that means he has
completed 2 full years by the start of the financial year 01/04/04.

To know the value in I have to somehow get it to work through the following
information.

Less than 5 years = 24 days
5 years but less than 10 years = 26 days
10 years but less than 15 years = 28 days
15 years but less than 20 years = 29 days
20 years and over = 30 days

Clearly the value in the cell under leave for john would be 24 and under
Fred it would be 28.

Is this too hard for a formula, should I be looking to VB ?

any guidance would help.

M.
 
MAS

There's no really best way for almost anything in XL, and
there is probably a way better way than this one:

=IF(CEILING(D2+1,5)=5,24,IF(CEILING(D2+1,5)=10,26,IF
(CEILING(D2+1,5)=15,28,IF(CEILING(D2+1,5)=20,29,30))))

But it works...

Cheers
Juan
 
Hi,

Using your table as an example, and assuming you have it on Sheet 1, try
the following:

1) Set-up a table on Sheet 2, something like this:

Years Days
0 24
5 26
10 28
15 29
20 30

2) Enter the following formula in D2 of your table on Sheet 1, and copy
down as far as you need to:

=VLOOKUP(C2,Sheet2!$A$2:$B$6,2)

Hope this helps!
 
Guy's..

What Can I say, they both answer my question and both work well.

It's an age since I did any real work in VBA and then it was in ACCESS so I
was not looking forward to spending weeks getting back into it. You have
saved me the trouble and given me the incentive to start looking at
understanding ALL the funtions in EXCEL, not just the few I use every day.

Cheers.

MAS
 
A little shorter than Juan's reply
D2: =CHOOSE(MIN(INT(C2/5)+1,5),24,26,28,29,30)
Click to expand...
...

If the goal is minimal but adaptable,

=24+LOOKUP(C2,{0;5;10;15;20},{0,2,4,5,6})
 
Just for fun. I think this can beat it by 7 characters, but it is
definitely not flexible. It assumes Integers in A1.

=MOD(7668316,34+INT(MIN(A1,20)/5))

Dana DeLouis
 
Dana DeLouis said:
Just for fun. I think this can beat it by 7 characters, but it is
definitely not flexible. It assumes Integers in A1.

=MOD(7668316,34+INT(MIN(A1,20)/5))
Click to expand...
....

You should have used MIN(INT(A1/5),4) to save one more character.
 
Using multiple IF's is a good idea. This technique just tries to shorten
the length of your characters, that's all. I can never get it right, so
this was one suggestion from a custom Fourier program.

=27-SIGN(4.5-A1)-SIGN(9.5-A1)-SIGN(14.5-A1)/2-SIGN(19.5-A1)/2

=IF(CEILING(D2+1,5)=5,24,IF(CEILING(D2+1,5)=10,26,IF
(CEILING(D2+1,5)=15,28,IF(CEILING(D2+1,5)=20,29,30))))
Click to expand...

Dana DeLouis
 
Another option to multiple If's:

=24+2*(A1>=5)+2*(A1>=10)+(A1>=15)+(A1>=20)

Dana DeLouis
 
Another option to multiple If's:

=24+2*(A1>=5)+2*(A1>=10)+(A1>=15)+(A1>=20)
Click to expand...
...

Or

=24+2*((A1>=5)+(A1>=10))+(A1>=15)+(A1>=20)

which doesn't save any keystrokes, but uses one fewer multiplication.
 
Back
Top