Annual Amount By Month
There are two tabs in my workbook right now. The first is where users will input information and annual budget number for consulting fees. The second tab spreads the annual budget by the 12 months. I don’t want the users to see or touch this tab containing the spread. Once the annual budget number is entered, I would like them to use a combobox to choice how they would like to spread the budget monthly. One choice is evenly which is the default and the other would be manually. If they choice manually I would like a userform or something where they than input each month’s amount that will total the annual budget. See attachment, this is all I got so far.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Spread Annual Number By Month With Varying Inputs
I'm almost done with the budget at work and now I need to spread the annual figure by month. Initially I was thinking of spreading it evenly by 12 months, i.e. $12k annual budget = $1k per month. However the boss doesn't want it that way. So I received inputs from certain managers as to when the bulk of the budget will be spent. Now I need to develop some type of formula so that once I input that amount let's say in June, then the rest of the month's will adjust accordingly. For example, using the example above, if I manually input $6k in June, then the remaining $6k from the budget will be spread out evenly through the other 11 months. Is there some clever way to do this or is just a matter of multiple IF statements?
View Replies!
View Related
Computing Annual Maxima
I have daily streamflow data for a large number of years. One column is the date and the second column is the streamflow value. I want to compute the maximum value and the 3-day maximum (largest average value for any consecutive 3-day period)for each water year (starts on October 1 and ends on Sept 30). Of course the number of days each year varies by one during leap years.
View Replies!
View Related
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
View Replies!
View Related
Show Trends To Forecast Annual Inventory
I have 200 items that have 22 months of usage information each and need to: 1. In one column, show trend (up or down) based on the 22 months' activity for each item. 2. In another column, show/ forecast how much inventory will be needed for the next 12 months.
View Replies!
View Related
Compiling Monthly And Annual Data On One Sheet
I have been trying to compile some monthly and annual data from our main sheet to a FY09 sheet (for the fiscal year 2009). The 2 tabs in the uploaded example are the Distribution tab which we use to track the status of every item and the FY09 tab where I need the totals to be calculated for each month as well as the entire year. I have tried several formulas I found while searching the forum but I can not seem to get any of them to work, (I am sure it is because I don't understand them very well). Since any formulas used will need to be copied 200 or so times, I would really like a VB solution which should also reduce the physical size of the file. I also tried a pivot table but I do not think it will show everything I need. Basically, I need all the items separated by month on the FY09 tab. Then column 'F' on the Distribution tab needs to be summed up for each item in column 'D' of the FY09 tab for the respective month and multiplied by the respective item price in column'C' with the total value going in column 'E'. Column 'D' and 'E' need to be summed up for each month and cumulative for the entire year. There needs to be an average items and value for each month as well as for the year.
View Replies!
View Related
Calculate APR (Annual Percentage Rate)
I am trying to calculate APR (Annual Percentage Rate) for a mortgage loan that has a balloon feature. I have tried to the the RATE function but it only gives me the APR for a loan that is ammortized over 30 yrs and paid in 30 years. I need the APR for a loan that is ammortized for 30 years with a baloon in 5 years.
View Replies!
View Related
Monthly Increase Based On Annual Target
I am trying to figure a formula that will give me a monthly increase with a annual target. Example: 2006 sales were $100,000 and December 2006 sales were $9,000. For 2007 I would like a 10% increase in sales which means I should end up with $110,000 at the end of 2007. The formula should figure a gradual sales increase each month until December 2007. I am assuming the business sales trend is upward with no seasonality so that January 2007 should be the annualized increase over December 2006. . I have used the formula =100000*((1+.1)^(1/12)-1) which works for 2007, but it gives me a large jump between December 2006 and January 2007 and then a slight increases each month.
View Replies!
View Related
Calculate Monthly & Annual Returns Of Security Or Index
I feel pretty dumb asking a basic math question but I couldn't find the answer anywhere. I would like to calculate the monthly & annual returns of a security or index. When trying to calculate various indexes to insure my math was correct, my numbers never match that of yahoo or bloomberg's. If you could just start me down the path,
View Replies!
View Related
Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)
I have log data in two columns: Column A: Date/time (at 30 minute intervals) Column B: Numeric data On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C. The end of the range is determined by the month in the current row. I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month. I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach. Manual =IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month Based On Workdays
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them. =IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
View Replies!
View Related
Compound Rate: Annual Growth Rate %
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight. r = 100((Y/X)^(1/n))-1) So for X = 200, Y = 750, n = 10, we have r = 100((750/200)^(0.1))-1) = 14.1309%
View Replies!
View Related
Automatically Bold And Highlight The Current Month’s Total And Month Name
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name. Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month
function in a spreadsheet that will list all of the days in a given month automaticaly with the entry of the 1st of the month only. Ex; 10/01/05 entered dated 10/02/05 auto fill 10/03/05 " . " . " 10/31/05 end of auto fill I would like the function to stop filling dates at end of the month even for shorted months such as Feb.
View Replies!
View Related
Auto Format Spreadsheet With Various Rows Month To Month
I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month. Goal: I need to format the spreadsheet to make it more readable and have been assigned the task of: 1 - Inserting a blank row between each row that contains data and filling in with color. 2 - Resizing the blank row to make it look like a "thick" border. 3 - Auto adjusting the columns to correct size. 4 - The last column contains comments and needs to be wrapped text. 5 - All of this needs to fit on 1 sheet (landscape). Issues: 1 - Each month there will be a different number of rows. 2 - I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls? I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.
View Replies!
View Related
Adding Or Subtracting One Month To A Month Number
I have forumlas that will look at this cell and take action of the month in a different cell is either 1 month greater (Frontmonth+1) or less (Frontmonth-1) than "Frontmonth". As we approach December I'm realizing that logic will breadown since the FrontMonth+1 would be 13, not 1 (January) Is there a way to get excel to add 1 month to just the month number so that if Frontmonth = 12, Frontmonth+1 would return 1, not 13?
View Replies!
View Related
Results By Month And Week Of Month
I have a range of data which is as follows: Week in month: 1 1 1 5 Site: 01/03 02/03 03/03 etc 30/03 etc Leeds 10 9 15 20 Manchester 8 5 1 2 Etc Here's what I need to produce: March 08 April 08 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Leeds Manchester I need to sum week 1 to 6 for each month Mar, Apr and so on. The different sites are in the same order so that doesn't matter too much.
View Replies!
View Related
Last Day Of Month Reverts To First Of Month
I'm doing a web query that brings down stock dates/prices. The dates come down as mm/yyyy. I then format the column as mm/dd/yy which changes the date to mm/01/yy. Then I do a loop that converts the dates to the date of the last weekday of the month. I see the changed dates written back to their cells, but by the time I leave the called conversion subroutine and hit the break on the next line of code, the dates have all reverted back to the first of the month. In a case like this, I use the Step mode in VBE to step through the code while I have the worksheet the code is acting on visible. That way you can actually see which line in the code converts the value back to the original value or if indeed it does put the eomonth date in the active cell. By the way, I'd suggest you use the equivalent of the eomonth worksheet function directly in the macro rather than pasting the date to another cell and reading the eomonth from another cell containing the formula for eomonth.
View Replies!
View Related
Dollar Amount
How can I display the $xx.xx into a statement? I have these $ amounts in column A: 99.12 107.65 14500.99 Have the result in column B as: Please Pay Exactly$00000xx.yy (where xx is tenth, hundreds, thousands and yy is the cent) 00000 is to prevent forgery and to have the results in column C using same column A: Please Pay Exactly$0000xxDollars and yycents For example like Please Pay Exactly$000099Dollars and 12cents or Please Pay Exactly$000014500Dollars and 99cents
View Replies!
View Related
Replacing $ Amount With X
I need a formula that will replace a dollar amount with an X. In other words...I have 10,000 cels and half of them have dollar amounts in them...I would like to replace them with the letter X without having to manually do it...
View Replies!
View Related
Calculation A Total Amount
I am trying to write a calc for a total an amount based on a list choice Here is the Set up Fields A1 thru A255 are number 1 to 255 Fields B1 thru B255 are number 255 to 1 Field C1 is a List box set using A1 to A255 and its list Field D1 is set as the needed Calculation When you choose a number in C1 the D1 calculation should total the amount of Fields B* to B1, * being the corrisponding B field to the A field from the list Example C1=100 (Field A100) D1=Sum of Fields B100 to B1
View Replies!
View Related
Amount To Words Conversion
I am using excel for invoice making...In the end i have a total amount. I need to type it in words for every invoice at present. How can i convert it to words automatically? Is there any formula to it? Or is there anything else? Please suggest me friends.
View Replies!
View Related
Rasterize Displayed Amount
I have a column with dates in the following format: dd/mm/yyyy h:mm but im doing an XML export and excel only exports in text format. I would like to somehow resterize or only use the displayed amount instead of the text which is exported (ie- 37805.1640277778)
View Replies!
View Related
Subtract X Amount Every Y Years
I am building an investing strategy model and am looking for a fomula or combination of formulae that would subtract an amount (lets say a 100) every so many years (lets say 10). Data is set up horizontaly, i want to be able to set how much will be subtracted and how often in a "control panel" next to other inputs and variables. I am not proficient in Macros and most often have trouble with them so if its possible to solve this without using any that would be great.
View Replies!
View Related
Dollar Amount Inventory
I have a total inventoried product dollar amount in a cell. I have 4 other cells with different dollar amounts in each for product I inventory out! In the seperate balance column, as I use a letter ...
View Replies!
View Related
Add Amount In Column
Trying to add a formula in my spreadsheet that will add every amount in column AQ8:AQ200 if the date in AQ8:AQ200 is march. The format for the date is 3-12-09. Here is what I'm trying. =SUMIF(AU8:AU200,MONTH=3,AQ8:AQ200)
View Replies!
View Related
|
TRACKER
