To many formulas??

  • Thread starter Thread starter JC2003
  • Start date Start date
J

JC2003

I used this formula for an employee form
=IF(B6>37987,0,IF(B6>37951,1,IF(B6>37914,2,IF(B6>37878,3,IF(B6>37841,4,IF(B6>37805,5,IF(B6>37768,6,IF(B6>37732,7,""))))))))

And I need three more, but when I ad one, it doesn't work... Are there
too many?
 
Hi,



Yes Excel is limited to 7 nested functions. To go around it create a user
defined function
 
JC,

You need a lookup table set up similar to this:
Try this in a new workbook to test it out (may need some tweaking)
In cells G1:H9, place the following:
Col G Col H
0 0
37732 7
37768 6
37805 5
37841 4
37878 3
37914 2
37951 1
37987 0

You can add as many rows as you want to the table as long as
you keep it in numerical order.

Now in cell C6 (or any cell for that matter), place the following
formula:
=VLOOKUP(B6,G1:H9,2,TRUE)
Then place values in B6 to test it out.

The above is a Vlookup table with the last argument set to true.
What this does is "look up" the value placed in B6 that's equal to
but not over the next value in the table. The table "has" to be in
numerical order for this to work properly.
By tweaking, I meant that your original formula was looking for
something that was "over" a value. You may have to increase the
lookup value in the table by one to get the results you desire.

Post back if you have any problems with this method.

John
 
Thx thats super! But... erm.. he does that till 1/1/2003 so 37622 (they
get 10 days off) and then the employee get 15 days of if he works here
from on and before that 1/1/2001 (36892).... Thx!!
 
JC

Create a table with your data in like this:
37732 7
37768 6
37805 5
37841 4
37878 3
37914 2
37951 1
37987 0
Make sure these are entered in separate cells. You can then use VLOOKUP to
find your results:
=VLOOKUP(D15,D5:E12,2,1)
assuming the cell with your figure is D15 and the table of data is D5:E12

Andy.
 
This should get you started: =VLOOKUP(C6,{37915,2;37952,1;37988,0},2)
You can extent the array add in front of 37915 but watch the punctuation -
comma and semicolon.
Also note the numbers 37915 etc are one more than yours.
Bernard
 
I got this now, and it works perfect
=IF(B13<36892,15,IF(B13<37622,10,INT((37987-B13)/36.5)))

Thx you guys!
 
JC2003 said:
I got this now, and it works perfect
=IF(B13<36892,15,IF(B13<37622,10,INT((37987-B13)/36.5)))
Click to expand...

Very good implementation. I couldn't spot all the real life logic, but had a
feeling it was "something with dates" :-)

Best wishes Harald
Excel MVP
Followup to newsgroup only please
 
Back
Top