If, And, Or Confusion!

[amyindubai]

Hello - I'm confused as to which function, or combination of functions, I should be using to get the figures I want... I am using excel 2007.

I am working on employee payroll budget for the next 60 months (5 years) and need to factor in a salary increase if the employee has been with the company for at least 9 months in that particular calendar year.

I have a fixed salary increase for each year of employment (year1=3%, year2=5% ect).

Employees who work for a minimum of 9 months are entitled to the increase.

Increases will be effective in the Jan payroll each year (Month 13,25,37,49).

So I need a formula, starting in month 13, that says the following in Month 13 :

If Start month is less than 4 use $4000 plus 5%
If start month is equal to 13 and greater than 4, use $4000
If start month is greater than 13 use 0

I Can do all of the above separately but run into problems when combining... I tried the if/and function but it is limited to only a true and false output and I need 3 different outputs...

Any help is greatly appreciated!

[abousetta]

Hello,

There are several approaches that can be used, but I am having trouble following the logic. Could you upload a dummy workbook showing the results if you manually calculated it.

abousetta

[Mallycat]

I Can do all of the above separately but run into problems when combining... I tried the if/and function but it is limited to only a true and false output and I need 3 different outputs...

The trick is to do the steps separately, then combine them. Do this in separate starting cells, then in another results cell create one formula that gives you the answer that refers to your starting cells. Then in you results cell, replace the references to the starting cells with the formulas from those starting cells.

Also note you can next if statments together like this.

=if(A1=5,Answer, Alternative answer). then just replace Alertnative answer with another if statement like this.

=if(A1=5,Answer, if(A1=3,Alternative answer 1, Alternative answer 2).

[Richard Buttrey]

.. and just to correct a small typo make sure you have as many closing brackets as there are IFs

=if(A1=5,Answer, if(A1=3,Alternative answer 1, Alternative answer 2))

[amyindubai]

Hello and thank you for your help thus far...

Attached is a sample workbook with:

1) An assumptions page

2) An example of how the "actual payroll" would look based on the assumptions (manually enetered/no formulas)

3) An attempt to solve the problem that did not work - I am fine until I get to month 13 when I have to decide whether or not the employee has earned the increase for that year.

Thanks for your continued assistance!

[Richard Buttrey]

Hi,
You have a circular argument in your note re row 13. You want a formula in C13 and one of its condition includes the value of C13, i.e. SUM(C2:C13>40000).

Now there's nothing inherently wrong with a circular formula but it's usually best to try and avoid them. And your assumption sheet doesn't seem generalised enough to reflect the Employee title.

It seems to me that you would be better putting actual month end dates on row 2 of the Payroll Budget, adding extra columns at the beginning for Start date, starting salary, four columns for the Y1-4 % increases, and 4 columns which contain formulae that work out when the increases should cut in.

With that basic data then you could more easily construct formulae on the payroll budget sheet using the new data mentioned above and using the dates on row 2 in the IF tests you'll need.

Regards

[watersev]

hi amyindubai, please check attachment, option for formula in C2:C61 in "An attempt" sheet. Change value in A2. The formula has set starting actual salary equal to 5000, amend it if it is different

[watersev]

please check attachment, Sheet "An Attempt", column D formula. The array in question can be represented as a table G19:K24. Hope this helps.

[amyindubai]

Thank you very much Watersev - it's crystal clear now! Much apprecaited!!