If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula to calculate partial payments
Hi, I work at a college. I am trying to build a form that will replace our
8-copy-carbonless form. The academic calendar is 9 payperiods that do not start and end with months, but range from 30 to 31 day periods. For this academic year the start was 08/21/06 and the end is 05/20/07. People start and end on any day (paid by a 7 day week). Each day of each payperiod would be a different fraction of the 9.000000 total. The formula that I am trying to build takes the total salary for 9 months, divides that by 9 multiplies that 1/9th by the appointment % which gives me what that person will "actually be paid" for each pay period. Those parts I have just fine. This is where I'm stuck. I want to take that "actual pay" and multply it by the fraction of 9.000000 that the start and end date represent for the total encumbrance needed. I have built a lookup table that gives each date it's fraction value. for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay period is 31 days). So if a person earned $32,000/9=$3,555.56 per pay period with a 100% appointment and worked from 08/23/06 to 09/24/06 they would earn $3,555.56*1.033333 or a total of $3,674.07. Thank You in advance, Jean |
#2
|
|||
|
|||
Formula to calculate partial payments
I might misunderstand you, but assuming you have the Payperiod salary
in A1, the start date in A2, the end date in A3, your lookup table is called DatePortion (or whatever) with the fraction value in column 2, could you not use: =A1*(vlookup(A3,DatePortion,2,false)-vlookup(A2-1,DatePortion,2,false) Scott Jean wrote: Hi, I work at a college. I am trying to build a form that will replace our 8-copy-carbonless form. The academic calendar is 9 payperiods that do not start and end with months, but range from 30 to 31 day periods. For this academic year the start was 08/21/06 and the end is 05/20/07. People start and end on any day (paid by a 7 day week). Each day of each payperiod would be a different fraction of the 9.000000 total. The formula that I am trying to build takes the total salary for 9 months, divides that by 9 multiplies that 1/9th by the appointment % which gives me what that person will "actually be paid" for each pay period. Those parts I have just fine. This is where I'm stuck. I want to take that "actual pay" and multply it by the fraction of 9.000000 that the start and end date represent for the total encumbrance needed. I have built a lookup table that gives each date it's fraction value. for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay period is 31 days). So if a person earned $32,000/9=$3,555.56 per pay period with a 100% appointment and worked from 08/23/06 to 09/24/06 they would earn $3,555.56*1.033333 or a total of $3,674.07. Thank You in advance, Jean |
#3
|
|||
|
|||
Formula to calculate partial payments
Hi Scott, Thank YOU for taking your time to answer my messy (unclear) question.
Your formula has gotten me closer than 4 days of off and on trying. When I put it in (with my cell info) it told me I was missing a parenthesis. In my spreadsheet it is Payperiod Salary= I12; Start Date= B12 and End Date= C12. My formula looks like this: =I12*(VLOOKUP(C12,DatePortion,2,FALSE)-(VLOOKUP(B12-1,DatePortion,2,FALSE) I tried putting in a parenthesis in a few different places...but then I got #NAME? for an answer. If you can see where I went wrong, please let me know. Thank You again, Jean "Scott" wrote: I might misunderstand you, but assuming you have the Payperiod salary in A1, the start date in A2, the end date in A3, your lookup table is called DatePortion (or whatever) with the fraction value in column 2, could you not use: =A1*(vlookup(A3,DatePortion,2,false)-vlookup(A2-1,DatePortion,2,false) Scott Jean wrote: Hi, I work at a college. I am trying to build a form that will replace our 8-copy-carbonless form. The academic calendar is 9 payperiods that do not start and end with months, but range from 30 to 31 day periods. For this academic year the start was 08/21/06 and the end is 05/20/07. People start and end on any day (paid by a 7 day week). Each day of each payperiod would be a different fraction of the 9.000000 total. The formula that I am trying to build takes the total salary for 9 months, divides that by 9 multiplies that 1/9th by the appointment % which gives me what that person will "actually be paid" for each pay period. Those parts I have just fine. This is where I'm stuck. I want to take that "actual pay" and multiply it by the fraction of 9.000000 that the start and end date represent for the total encumbrance needed. I have built a lookup table that gives each date it's fraction value. for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay period is 31 days). So if a person earned $32,000/9=$3,555.56 per pay period with a 100% appointment and worked from 08/23/06 to 09/24/06 they would earn $3,555.56*1.033333 or a total of $3,674.07. Thank You in advance, Jean |
#4
|
|||
|
|||
Formula to calculate partial payments
You indicated that you had a lookup table with the date and the
fractional value. I presumed that the date was in the first column and the fractional value in the second, and than you named this range. :-) You could substitute the boundaries of this range, ie. if it's on another sheet, go 'Sheet 2'!$A$2:$B$273. The date has to be in the first column, but the fractional value can be in any column thereafter, you just have to adjust the column referenced in the VLOOKUP() formula. In other words, I just picked a random name "DatePortion" to represent that range. My apologies for not being clearer. So you can either name your table something and put that name in the formula, or substitute the location of your lookup table like the above, namely 'Sheet 2'!$A$2:$B$273 (or wherever it is). You might have a slight hiccup for the formula I provided as it looks at the day before your start day for the value. (That is, it assumes you want the period from B12 to C12 inclusive, so normally you'd subtract the day before B12) The hiccup would be if B12 was 8/21/06... you'd need to add 8/20/06 with a fractional value 0 to the beginning of your lookup table. This may have been an incorrect assumption. I looked at your data and thought it looked more like you wanted $3,555.56*(1.03333 - 0.096774). Hopefully that helps more than it confuses, Scott Jean wrote: Hi Scott, Thank YOU for taking your time to answer my messy (unclear) question. Your formula has gotten me closer than 4 days of off and on trying. When I put it in (with my cell info) it told me I was missing a parenthesis. In my spreadsheet it is Payperiod Salary= I12; Start Date= B12 and End Date= C12. My formula looks like this: =I12*(VLOOKUP(C12,DatePortion,2,FALSE)-(VLOOKUP(B12-1,DatePortion,2,FALSE) I tried putting in a parenthesis in a few different places...but then I got #NAME? for an answer. If you can see where I went wrong, please let me know. Thank You again, Jean "Scott" wrote: I might misunderstand you, but assuming you have the Payperiod salary in A1, the start date in A2, the end date in A3, your lookup table is called DatePortion (or whatever) with the fraction value in column 2, could you not use: =A1*(vlookup(A3,DatePortion,2,false)-vlookup(A2-1,DatePortion,2,false) Scott Jean wrote: Hi, I work at a college. I am trying to build a form that will replace our 8-copy-carbonless form. The academic calendar is 9 payperiods that do not start and end with months, but range from 30 to 31 day periods. For this academic year the start was 08/21/06 and the end is 05/20/07. People start and end on any day (paid by a 7 day week). Each day of each payperiod would be a different fraction of the 9.000000 total. The formula that I am trying to build takes the total salary for 9 months, divides that by 9 multiplies that 1/9th by the appointment % which gives me what that person will "actually be paid" for each pay period. Those parts I have just fine. This is where I'm stuck. I want to take that "actual pay" and multiply it by the fraction of 9.000000 that the start and end date represent for the total encumbrance needed. I have built a lookup table that gives each date it's fraction value. for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay period is 31 days). So if a person earned $32,000/9=$3,555.56 per pay period with a 100% appointment and worked from 08/23/06 to 09/24/06 they would earn $3,555.56*1.033333 or a total of $3,674.07. Thank You in advance, Jean |
#5
|
|||
|
|||
Formula to calculate partial payments
Hi Scott. Between you and God I am almost there! Thank You for again taking
your time to help me. I am such a novice at building formulas. It wasn't that you weren't clear in your 1st answer...I just don't know what I am looking at a lot of the times! You broke it down so well here that I am able to understand what I am looking at and that is light years away from where I was yesterday before your first response. Thank You Again, Jean "Scott" wrote: You indicated that you had a lookup table with the date and the fractional value. I presumed that the date was in the first column and the fractional value in the second, and than you named this range. :-) You could substitute the boundaries of this range, ie. if it's on another sheet, go 'Sheet 2'!$A$2:$B$273. The date has to be in the first column, but the fractional value can be in any column thereafter, you just have to adjust the column referenced in the VLOOKUP() formula. In other words, I just picked a random name "DatePortion" to represent that range. My apologies for not being clearer. So you can either name your table something and put that name in the formula, or substitute the location of your lookup table like the above, namely 'Sheet 2'!$A$2:$B$273 (or wherever it is). You might have a slight hiccup for the formula I provided as it looks at the day before your start day for the value. (That is, it assumes you want the period from B12 to C12 inclusive, so normally you'd subtract the day before B12) The hiccup would be if B12 was 8/21/06... you'd need to add 8/20/06 with a fractional value 0 to the beginning of your lookup table. This may have been an incorrect assumption. I looked at your data and thought it looked more like you wanted $3,555.56*(1.03333 - 0.096774). Hopefully that helps more than it confuses, Scott Jean wrote: Hi Scott, Thank YOU for taking your time to answer my messy (unclear) question. Your formula has gotten me closer than 4 days of off and on trying. When I put it in (with my cell info) it told me I was missing a parenthesis. In my spreadsheet it is Payperiod Salary= I12; Start Date= B12 and End Date= C12. My formula looks like this: =I12*(VLOOKUP(C12,DatePortion,2,FALSE)-(VLOOKUP(B12-1,DatePortion,2,FALSE) I tried putting in a parenthesis in a few different places...but then I got #NAME? for an answer. If you can see where I went wrong, please let me know. Thank You again, Jean "Scott" wrote: I might misunderstand you, but assuming you have the Payperiod salary in A1, the start date in A2, the end date in A3, your lookup table is called DatePortion (or whatever) with the fraction value in column 2, could you not use: =A1*(vlookup(A3,DatePortion,2,false)-vlookup(A2-1,DatePortion,2,false) Scott Jean wrote: Hi, I work at a college. I am trying to build a form that will replace our 8-copy-carbonless form. The academic calendar is 9 payperiods that do not start and end with months, but range from 30 to 31 day periods. For this academic year the start was 08/21/06 and the end is 05/20/07. People start and end on any day (paid by a 7 day week). Each day of each payperiod would be a different fraction of the 9.000000 total. The formula that I am trying to build takes the total salary for 9 months, divides that by 9 multiplies that 1/9th by the appointment % which gives me what that person will "actually be paid" for each pay period. Those parts I have just fine. This is where I'm stuck. I want to take that "actual pay" and multiply it by the fraction of 9.000000 that the start and end date represent for the total encumbrance needed. I have built a lookup table that gives each date it's fraction value. for example, 08/23/06 = 0.096774 and 09/24/06 = 1.033333. (The first pay period is 31 days). So if a person earned $32,000/9=$3,555.56 per pay period with a 100% appointment and worked from 08/23/06 to 09/24/06 they would earn $3,555.56*1.033333 or a total of $3,674.07. Thank You in advance, Jean |
Thread Tools | |
Display Modes | |
|
|