HI,
The first solution is correct, you are using it in a wrong way. Let me break that up for you- =MonthlySalary *(if(DaysWorked > 30, 30, DaysWorked)/30) You just want to multiply the Monthly Salary with Days Worked. What you are using in the sheet is =D38*(IF(D35>30,30,D35/30)), whereas you should have used, =D38*(IF(D35>30,30,D35)/30). You are making the 30 devisor as part of the IfFlase part in the If statement. The if statement simply returns 30 if the DaysWorked is greater than 30, if not - returns the DaysWorked and then we divide it by 30, keep that /30 out of if(). Ignore the INT() stuff. That is wrong. Regards, Ajit From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Ashish Pradhan Sent: Sunday, June 28, 2009 3:01 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Help with Attendance Tracker & Salary Calculation Hi Ajit Sorry to trouble you again.. Getting Errors with the solution. Am attaching the excel file using both the solutions as suggested. I think I am missing something really simple here but am all at sea. Thanks Ashish On 6/28/09, Ashish Pradhan <ashlyprad...@gmail.com> wrote: Hi Ajit Thanks for the prompt response. Let me try your solution and revert back. (I thought I was missing something, just wasnt sure if it was relatively easy or a bit complicated) Thanks once again. Ashish "Of what use is freedom if it does not include the freedom to make mistakes" -- Mahatma Gandhi On 6/28/09, Ajit Navre <aquas...@gmail.com> wrote: HI, Your post itself has the answer. You say that even-if the person has worked all the days in a month, salary Is paid for 30 days. That is you are assuming a month to be of 30 days. For months with >30 days in them, the calculation of yours (Monthly Salary * (No. of Days Present / 30)), yields salary even for the days that are > 30. So the excess that the calculation shows is the salary for the 31st day. Fix is, (Monthly Salary * (if(No. of Days Present > 30, 30, No. Of Days Present) / 30)). Other way to put it would be - (Monthly Salary * Int(No. Of Days Present / 30)). Regards Ajit From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Ashish Pradhan Sent: Sunday, June 28, 2009 12:46 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Help with Attendance Tracker & Salary Calculation Hello I need help with some calculations in the attached excel file. What I am trying to do is to calculate the salaries of personnel depending on the total number of days worked. My problem is with the sheets which carry data for Months which have 31 days. All Salary Calculations are done on 30 day basis irrespective of the number of days in a month. (If there are holidays, salary is still paid for 30 days and similarly if a month has 31 days or 28 days, Salary is paid for 30 days) The attached excel file contains two work sheets - Sheet "June" and Sheet "July" I will try to articulate my problem as: Sheet "July" ---- All calculations seem fine except for Column K, Column M and Column N (Highlighted in Red). Here, the persons have worked for all 31 days of the month. Now using the formula (Monthly Salary * (No. of Days Present / 30)), I get a figure which is more than the persons total salary. Is there any way to resolve this? Thanks in advance... -- -- Ashish "Of what use is freedom if it does not include the freedom to make mistakes" -- Mahatma Gandhi --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
