combine IF formulas to calculate payroll/mo based on start term dates

[ptell8019]

Hello - I'm having trouble combining IF formulas to calculate payroll per month based on term date and start dates for base salary, increases, and bonuses.


I calculated each one separately and they worked, but when I tried to combine them it wasn't calculating correctly. My experience with Excel is intermediate, so I'm a little stuck at this point and need some help.


Below is a link for the spreadsheet with an example of data. Please help.


https://docs.google.com/spreadsheet/ccc?key=0Ar6kpAQ21-fcdFJ0LWRHZjZ6X2NBa0J1WkR0MExfYUE

 

[fred]

Briefly looked at your spreadsheet. Here is my observation


your "actual" formula is just a long "if" function when the "base", "INCR" and "misc" are all independently working so your "if" formula should have somewhere "+" with in but i don't see any. May be that's reason why your "correct" isn't the same as your "acutal" formula line.

 

[ptell8019]

yes, i thought that was the problem too, but i tried adding "+", but it doesn't work.

 

[SirJB7]

Hi, ptell8019!

I agree with fred respect of the sum (+), but despite of that I have one previous doubt.

In your uploaded file you state the actual and the correct values for each month, many actual values are wrong (that's why you're here), but many correct values don't seem to be correct in my opinion.

Please check my new correct values and tell me if I'm wrong.

They're calculated proportional to net days, even a month isn't complete they include incr and bonus, it's ok?


new correct? $0 $952 $2.500 $2.552 $2.600 $2.600 $3.600 $2.600 $2.600 $2.600 $1.418 $0

correct $0 $952 $2.500 $2.552 $2.500 $2.500 $3.500 $2.500 $2.500 $2.500 $1.364 $0

actual $0 $952 $2.500 $2.500 $2.500 $2.500 $2.500 $2.500 $2.500 $2.500 $1.418 $0


Regards!

 

[ptell8019]

SirJB7: Yes, you're right about the correct row. I fixed it. I also went back in and added the (+) sign between the IF arguments, but the calculation is still incorrect.


Do i need to change the IF arguments to include a true and false and then the "+" sign? The IF formula currently only includes the logic and true (does not include the false formula).

 

[SirJB7]

Hi, ptell8019!

Give me a little and I'll send you the uploaded book modified.

I didn't want to work before your confirmation... january's our summertime ;-)

Regards!

 

[SirJB7]

Hi, ptell8019!

Check if it works: http://www.2shared.com/document/BgPiItA4/Payroll_formula__combine_IF_fo.html

My modifications in green: new values input, new formulaes, and detailed calculations.

Regards!

 

[ptell8019]

Perfect, it works! I have not looked at all the details, but I'll probably go through it carefully and see what you did. Thank you!!!

 

[SirJB7]

Hi, ptell8019!

I just separated the three items involved in calculation, and then joined them in a simple addition. Each formula has the structure "if(dates_off_range, 0, if(whole_period, full_value, <proportional_value-salary-incr/full_value-bonus-&#62)".

Just advise if need more details.

Regards!

 

[ptell8019]

OK. Thank you.