Formula to calculate pro-rate + 1 month

Hi all,

I'm not an Excel expert but I am wondering how I might accomplish the
following.

I work at an ISP and lets assume a customer wants our 256 x 256
package which is $31.00 month.

Obviously, if they sign up on say the 10th of the month, our billing
system bills them the 10th - End of Month + 1 FULL MONTH.

I'm wondering how I can put say $31.00 + $22.95 (phone service charge
per month) into a formula and have it figure out the pro-rate
depending on which date I use + the 1 full month?  Make sense?

I need a formula to calculate pro-rate (so let's use the 10th) 10th
through end of the month + 1 full month and have different figures/
sums depending where in the cycle it falls.

I hope this makes sense.  Let me know if I need to clarify and if you
have any ideas on how to accomplish this I am all ears.

Kind regards and many thanks in advance,

Steve Ryan

0
mrsteveo (1)
10/9/2007 10:46:54 PM
excel 39879 articles. 2 followers. Follow

3 Replies
301 Views

Similar Articles

[PageSpeed] 51

For October if the customer started his/her plane on the 10th and the price 
is in A1

=(DATE(2007,10+1,0)-DATE(2007,10,10)+1)*(A1/DAY(DATE(2007,10+1,0)))

with the month number in B1 and the day in C1


=(DATE(2007,B1+1,0)-DATE(2007,B1,C1)+1)*(A1/DAY(DATE(2007,B1+1,0)))

it's important to format the result as currency or number or else you will 
get a pseudo date



-- 


Regards,


Peo Sjoblom



"mrsteveo" <mrsteveo@gmail.com> wrote in message 
news:1191970014.577086.189940@k79g2000hse.googlegroups.com...
> Hi all,
>
> I'm not an Excel expert but I am wondering how I might accomplish the
> following.
>
> I work at an ISP and lets assume a customer wants our 256 x 256
> package which is $31.00 month.
>
> Obviously, if they sign up on say the 10th of the month, our billing
> system bills them the 10th - End of Month + 1 FULL MONTH.
>
> I'm wondering how I can put say $31.00 + $22.95 (phone service charge
> per month) into a formula and have it figure out the pro-rate
> depending on which date I use + the 1 full month?  Make sense?
>
> I need a formula to calculate pro-rate (so let's use the 10th) 10th
> through end of the month + 1 full month and have different figures/
> sums depending where in the cycle it falls.
>
> I hope this makes sense.  Let me know if I need to clarify and if you
> have any ideas on how to accomplish this I am all ears.
>
> Kind regards and many thanks in advance,
>
> Steve Ryan
> 


0
terre081 (3244)
10/9/2007 11:02:42 PM
This formula calculates the day of today's date 10/9/2007, divided by the 
number of days in October, 31 to give a proration multiplier of 9/31 = 
..290323.

=DAY(TODAY())/DAY(EOMONTH(TODAY(),0))
So, your full formula is: =DAY(TODAY())/DAY(EOMONTH(TODAY(),0))*($31.00 + 
$22.95) + $31.00 + $22.95 which equals $69.61 if service starts on 
10/9/2007.

You can leave out the $ signs and the decimal points. Also you can put the 
amounts $31 in cells A1 and $22.95  in B1 and use
=DAY(TODAY())/DAY(EOMONTH(TODAY(),0))*(A1 + B1) + A1 + B1

You have to install the Analysis Toolpak for Excel versions prior to Excel 
2007. The EOMONTH function is standard in Excel 2007. You can, of course, 
put any date in a cell, say A1 and substitute A1 in the formula for TODAY(). 
Then after prorating, you simply add one month's fees to that result.

"mrsteveo" <mrsteveo@gmail.com> wrote in message 
news:1191970014.577086.189940@k79g2000hse.googlegroups.com...
> Hi all,
>
> I'm not an Excel expert but I am wondering how I might accomplish the
> following.
>
> I work at an ISP and lets assume a customer wants our 256 x 256
> package which is $31.00 month.
>
> Obviously, if they sign up on say the 10th of the month, our billing
> system bills them the 10th - End of Month + 1 FULL MONTH.
>
> I'm wondering how I can put say $31.00 + $22.95 (phone service charge
> per month) into a formula and have it figure out the pro-rate
> depending on which date I use + the 1 full month?  Make sense?
>
> I need a formula to calculate pro-rate (so let's use the 10th) 10th
> through end of the month + 1 full month and have different figures/
> sums depending where in the cycle it falls.
>
> I hope this makes sense.  Let me know if I need to clarify and if you
> have any ideas on how to accomplish this I am all ears.
>
> Kind regards and many thanks in advance,
>
> Steve Ryan
> 


0
Tyro (331)
10/9/2007 11:35:59 PM
Sorry, formula is wrong. It should be 
=(DAY(EOMONTH(TODAY(),0))-DAY(TODAY())+1)/DAY(EOMONTH(TODAY(),0)) to 
calculate the multiplier
..
It should be =DAY(TODAY())/DAY(EOMONTH(TODAY(),0))*(31 + 22.95) + 31 + 22.95
"Tyro" <Tyro@hotmail.com> wrote in message 
news:zfUOi.32255$eY.17843@newssvr13.news.prodigy.net...
> This formula calculates the day of today's date 10/9/2007, divided by the 
> number of days in October, 31 to give a proration multiplier of 9/31 = 
> .290323.
>
> =DAY(TODAY())/DAY(EOMONTH(TODAY(),0))
> So, your full formula is: =DAY(TODAY())/DAY(EOMONTH(TODAY(),0))*($31.00 + 
> $22.95) + $31.00 + $22.95 which equals $69.61 if service starts on 
> 10/9/2007.
>
> You can leave out the $ signs and the decimal points. Also you can put the 
> amounts $31 in cells A1 and $22.95  in B1 and use
> =DAY(TODAY())/DAY(EOMONTH(TODAY(),0))*(A1 + B1) + A1 + B1
>
> You have to install the Analysis Toolpak for Excel versions prior to Excel 
> 2007. The EOMONTH function is standard in Excel 2007. You can, of course, 
> put any date in a cell, say A1 and substitute A1 in the formula for 
> TODAY(). Then after prorating, you simply add one month's fees to that 
> result.
>
> "mrsteveo" <mrsteveo@gmail.com> wrote in message 
> news:1191970014.577086.189940@k79g2000hse.googlegroups.com...
>> Hi all,
>>
>> I'm not an Excel expert but I am wondering how I might accomplish the
>> following.
>>
>> I work at an ISP and lets assume a customer wants our 256 x 256
>> package which is $31.00 month.
>>
>> Obviously, if they sign up on say the 10th of the month, our billing
>> system bills them the 10th - End of Month + 1 FULL MONTH.
>>
>> I'm wondering how I can put say $31.00 + $22.95 (phone service charge
>> per month) into a formula and have it figure out the pro-rate
>> depending on which date I use + the 1 full month?  Make sense?
>>
>> I need a formula to calculate pro-rate (so let's use the 10th) 10th
>> through end of the month + 1 full month and have different figures/
>> sums depending where in the cycle it falls.
>>
>> I hope this makes sense.  Let me know if I need to clarify and if you
>> have any ideas on how to accomplish this I am all ears.
>>
>> Kind regards and many thanks in advance,
>>
>> Steve Ryan
>>
>
> 


0
Tyro (331)
10/9/2007 11:49:04 PM
Reply:

Similar Artilces:

NEWBIE question. Fields with calculations correct on form not showing up on report?
I have basic fields that are on a data entry form so when the clerk types in quantity and unit price the TOTALS field calculates this. When I look at or pull in the TOTALS field on my report it just shows $0's. How do I get the report to show what's on my form? On Thu, 24 Jan 2008 10:00:37 -0800 (PST), p-rat <osupratt@yahoo.com> wrote: >I have basic fields that are on a data entry form so when the clerk >types in quantity and unit price the TOTALS field calculates this. > >When I look at or pull in the TOTALS field on my report it just shows >$0's. How do I ...

Conditional formula results
I would like to caluclate MIN and MAX on a column of numbers based on text in another column. For example, the numbers are in column B and column Q has "test failed" or some other text in it or no text at all. I want to include values from column B and calculate the MIN and MAX if column Q has "test failed" in that row and in another cell, calculate MIN and MAX if column Q has anything other than "test failed". Any help you can offer is appreciated. Scott in Ottawa R1: =IF(Q1="test failed",MAX(B:B),"") S1: =IF(Q1="test failed...

MATCH formula #3
Thanks. I'll try out those suggestions. De -- da ----------------------------------------------------------------------- dah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=649 View this thread: http://www.excelforum.com/showthread.php?threadid=27191 ...

Formula Help #39
I am using a way to do the following column1 column 2 column 3 (2+3)+(3*5) 5+15 20 in the first column I want to be able to see the expresion in the second column I want to have the result of each parenthesis and in the final column the result Please help Thanks Stelios ...

New formula: = does not disappear
In an excel sheet, I am typing in a new formula: "=Master!C271". However, instead of showing what is in that cell, it shows the formula. Does anyone know how to fix this? Thanks! Hi Mike tools / options / view - untick formulas or the cell was preformatted as text, use edit / replace - look for: = replace with: = this tends to fix it Cheers JulieD "Mike" <Mike@discussions.microsoft.com> wrote in message news:78BE9332-7E2F-4DB9-8D84-DE485BB6A878@microsoft.com... > In an excel sheet, I am typing in a new formula: "=Master!C271". However, > ins...

Calculate credit card APR
I am using excel 07. I would like to know how to setup a formula to calculate credit card apr or a pre done form that i can download. I have been searching but can not find exactly what i am looking for. I would like to enter current balance, apr and have it calculate what the interest is then have it minus payment and then recalculate balance and have it continue for the next month. I am not sure if this is enough information but i hope it is a start. Any information would help. Thanks in advance. Rob Rob wrote: > I am using excel 07. I would like to know how to setup a formula ...

Need help creating a formula
I would like to create a formula that will check to see if there is value in one of two cells, multiply the found value by a value i another cell and return a total. Specifically: Cell B1 Cell B2 CellB3 Either B1 or B2 will contain a value, not both. If B1 has a value, it will be multiplied by the value in B37 and th result displayed in B3. If B2 has a value, it will be multiplied by the value in B38 and th result displayed in B3. Th -- storeman496 ----------------------------------------------------------------------- storeman4966's Profile: http://www.excelforum.com/member.ph...

Office 2004 will not install on MacBook Pro
Hi, I would be grateful for some help from any expert in the group. I have just bought a new MacBook Pro 17 inch. I have run Remove Office several times and tried to install my legitimate copy of MS Office:mac 2004 from disc. Each time I run Remove it tells me that there are files in Preferences. I select Remove and Empty Trash. I have also checked the Preferences folders and removed any Microsoft files manually. I have secure emptied the trash. And this just repeats... Every time I launch the installer it whirrs for a few seconds then quits. No amount of double clicking will r...

Calculate/Update Formulas
Lately, I have been having difficulties getting Excel to update existing formulas. I press F9 to update/calcuate and nothing happens. However, if I go to a particular cell with a formula, hit F2 to edit and simply press the enter key, the formula updates. What gives? Is this some type of virus? Thanks for any feedback. Hi could you post this specific formula? Also does hitting CTRL+ALT+F9 change the value -- Regards Frank Kabel Frankfurt, Germany Kevin wrote: > Lately, I have been having difficulties getting Excel to > update existing formulas. I press F9 to update/calcuat...

Payroll Calculator Excel Template
On the Payroll Calculator template under tax status, what do the numbers represent? Any suggestions on the template? ...

SFO 1.2 Client
Hi, I've installed SFO on two machines as per the instructions and all seemed to go well until I tried to get them to "Go Offline". In each case I accepted the default settings in the offline options screen and pressed OK. A download status box came up with the word "Initializing" then nothing happens for a few minutes and eventually I get an error message saying "you do not have sufficient permissions or access rights to perform this action". Everything seems to work fine when I'm online though. Client machines are Win XP/Outlook 2003 and Win 2K/O...

layout help-creating 2 pages on 8 1/2 x 11 paper for booklet
I am new to Publisher, so bear with me.... I am trying to create a booklet using 8 1/2 x 11 paper in positioned in landscape position. I would like to get 2 pages on one piece of paper, but with the text in a portrait position, if that makes sense. Can anyone guide me? Thanks. -- Lauren When you setup your publication as a booklet, the pages are presented in the correct order automatically. The half pages will be portrait. Have you tried to setup a booklet? If you have, what problems are you experiencing? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.or...

In HQ make works sheets formulas go cross tables..
It would be great to have more functionality in the HQ worksheet formula's if you add a supplier and they have a lot of items, but the costs are the same, a lot of time could be saved if you could copy the cost from the standard cost. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in...

formulas between worksheets
could any body tell me if it is possible to create a formula that allows you for example to input a number in worksheet 1 and for that number to automatically appear in worksheet 2? This formula on any sheet would make the cell equal to Sheet 1 A1 =Sheet1!A1 Mike "ollie" wrote: > could any body tell me if it is possible to create a formula that allows you > for example to input a number in worksheet 1 and for that number to > automatically appear in worksheet 2? thankyou so much > This formula on any sheet would make the cell equal to Sheet 1 A1 > > =She...

Calculating age
I have a Spreadsheet that contains a column of dates (birthdays and anniversarys)in the dd/mm/yyyy format. Across row 1 I have the years 2010, 2011, 2012 etc. At the intersection of the two I wish to insert a formula that will calculate the age or the anniversary. I would be grateful for any help. Thanks in advance. See your other post. Regards, Fred "Chris waller" <Chriswaller@discussions.microsoft.com> wrote in message news:0AFDC0A0-1B1B-47DE-AD7B-E77B64E995AB@microsoft.com... >I have a Spreadsheet that contains a column of dates (birthdays and > an...

trying to get day/month/year froamt while user enters year only
I am trying to get the end user to enter the year in question and then have the document return values in column A in dd/mmm/yyyy format and column B in Day of week format for all days in that year You would just use something like =DATE(2006,1,ROW()) copied down, but I am not clear where the year number is stored, and when the flip to column B happens. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RADIOOZ" <RADIOOZ@discussions.microsoft.com> wrote in message news:5A7C3C6F-8AEE-487F-8ADF-62F2234C3858@microsoft.com... > I ...

excel formula #11
I am working on a bonus chart and this is what I am trying to do: If an employee keeps his liquid tempatures under 40 degrees the recieve 100 points. If they are higher than 40 degrees they get points. the problem is they have two temperature check per week. I they are over on one of the checks they will not get the 100 points. can I have one formula to check both cells (Both checks) without usin an average between the two temps? I have tried playing with "IF formulas until I am blue in the face. Please help Thanks Mike Manick Longmont, Colorad -- Message posted from http://www.Exc...

Multiple Possibilitiy formula (IF or Table or Array)
Using XL 2000 work 2003 home Need easiest way to formula"ize" the following: Column (A) is the linked-cell value in an XL dropdown box. Column (B) is the value that column (A) needs to be converted to to upload to MS Access. Description (A) (B) Strongly Agree 2 4 Agree 3 3 Disagree 4 2 Strongly Disagree 5 1 No knowledge 6 0 (No Answer) 7 0 Therefore, the selection of "Strongly Agree" causes the linked dropdown box to equal 2 in column (A). I need the (B) value to equal 4 ...

looking for free software to calculate composite reliability?
Hello, At your possible convenience, might anyone please kindly answer my question? Thank you very much. I need to calculate composite reliability for constructs (latent variables). I know some people calculate the composite reliability manually. I am wondering whether anyone might happen to know any free software (open resource) that can help calculate composite reliability? Thank you very much. Please take care Caroline Caroline, Post how the manual calc is done, and then we can help. I have no idea what you mean by composite reliability.... HTH, Bernie MS Excel MVP ...

How to set up 2 diferent prices for 1 product?
Hallo. I need to set up special pricing for product. For example i sell bananas by 5$, but when they get not look good then I want to sell it by 1$. How to create the pricing mechanism for this situation. It’s not a special sales price because in the same time I can sell good bananas for a regular price. Ho to resolve that pricing situation in RMS? On May 2, 8:00 am, Martins <Mart...@discussions.microsoft.com> wrote: > Hallo. > > I need to set up special pricing for product. > For example i sell bananas by 5$, but when they get not look good then I > want to se...

How can I get yesterdays date in a custom footer &[DATE]-1 does .
How can I get yesterdays date in a custom footer &[DATE]-1 does not work try =today()-1 chuck <chuck@discussions.microsoft.com> wrote in message news:5A51D4C3-07A5-48A8-A594-09F51D4B46A6@microsoft.com... > How can I get yesterdays date in a custom footer &[DATE]-1 does not work Chuck - You will have to manually or programmatically change the displayed date in the footer. There are no page setup codes that will put a different date in a footer. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...

Printing an Email Message (but only 1 page)
Hi, when I go to print an email (in Microsoft Outlook), I press "file", then "print", and usually I get a window asking "Print pages -- "all" or "page numbers.." etc, but I don't always get this screen. Sometimes, (depending on the email), I get "print all pages, print only odd pages, or print only even pages". How can I only print the current page, all the time? Thank you! ...

Can I show a number like 1,935,674,923 as 1.9B?
I would like to reformat 10- and 11-digit the numbers (e.g. like 1,935,674,923 as 1.9B). Can this be done? Custom format: 0.0,,,"B" best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Dave" <Dave@discussions.microsoft.com> wrote in message news:262241F3-063E-4F97-B864-A629EC670112@microsoft.com... > I would like to reformat 10- and 11-digit the numbers (e.g. like > 1,935,674,923 as 1.9B). Can this be done? Dave, Use a custom format of #.#,,,"B" or 0.0,,,"B" depending on ho...

EXCEL 2007 Formula to calculate INTEREST only on a 3 month bridge
I am trying to calculate monthly INTEREST ONLY payment on a short term bridge/swing loan. Assuming an interest rate of 2.75%, paid monthly, Also assuming it will be required for a 3-6 month period, amount approx $500,000. Just switched to Excel 2007 but don't seem to be able to calculate using the formula builder. Not sure if it is compounded daily or monthly. You might want to check out the IPMT function. From the XL help file: IPMT(rate,per,nper,pv,fv,type) Rate is the interest rate per period. Per is the period for which you want to find the interest and must...

Turning 1 into One
(MS Excel 2000) Hi, Can I type "1" into a cell and get it automatically to come up as a "One" in another cell. Similarly can I type "2" into the same cell to come up as "Two" in the other? ... and if so how? A juicy red apple is nice but not every apple is red! http://www.wattersons.co.uk --- Message posted from http://www.ExcelForum.com/ njhagan, I'm not sure that this is what you want but if you enter : =LOOKUP(A1,{1,2,3,4,5,6,7,8,9,10},{"One","Two","Three","Four","Five","Six",&qu...