Multiple IF Statements

Hi guys,

I tried a read through previous posts, but I'm afraid none of them were able to clarify the matter for me.

I have been working away for a few hours, but not getting very far. I'll explain.

I'm trying to create a "simple" form that calculates a cost. I work in recruitment, so for example, if I recruit someone to work for a client and they transfer that temp to a permanent position, there is a cost involved that reduces on an incremental time-scale

The factors that make up the cost are as follows:

Full transfer fee: 10% of the yearly salary

Transfer Time-scales vs cost

14 Days or less = 90% of the Full transfer fee
Between 15 and 28 days = 80% of the Full transfer fee
Between 29 and 42 days = 60% of the Full transfer fee
Between 43 and 56 days = 40% of the Full transfer fee
Between 57 and 70 days = 20% of the Full transfer fee
Between 71 and 84 days = 10% of the Full transfer fee

So before I even attempted the full formula, I decided just to takes one basic statement first just to make sure I could get it right.

I have attached the sheet so you can see the required fields.

I started with:

Please Login or Register  to view this content.

Now that works fine, but as soon as I move beyond the 14 day minimum I have to take into account the range isn't as straight forward, and that's where I fall down. I have tried a few variations, but all seem to fail.

Is this something that's too complex for an IF statement, or merely to complex for me?

Any help or advice would be appreciated.

Steve

Steve, perhaps a LOOKUP will work here rather than embedding IFs:

C6: =($C$3*0.1)*LOOKUP(C5-C4,{0,15,29,43,57,71,85},{0.9,0.8,0.6,0.4,0.2,0.1,0})

The above will take the 10% * appropriate % based on days difference between C5 & C4.

The LOOKUP here finds the last value in the lookup_vector (the days boundaries) that is <= criteria value (C5-C4) and returns the associated value from the result_vector (the percentages) ... so if C5-C4 = 18 it would find 15 (2nd value) in the lookup_vector, the associated value in the result vector is 0.8 (2nd value)