Formula For Budget Variance
finding a way to compare the two budgets i.e 08/09 and 09/10 and if a new cost centre appears in 09/10 it will bring a yes in column G(New cc) and if the Cost Centre already exist in 08/09 it will bring a blankbut in both cases i want a variance in the next column H i.e 09/10 less 08/09.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Forecast An Estimated Budget Based On Original Budget And Percent Complete
In the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.
View Replies!
View Related
Budget Splitting Formula
I have to run payroll every two weeks, and part of the process involves calculating overtime for over 50 employees. The total amount of overtime hours for each employee is usually split on two or three budgets and after I calculate the budget splits, I have to enter these amounts into the payroll system. (The payroll system takes up to 2 decimal spaces.) I have a spreadsheet set up for each employee, but what I've noticed is that the sum of the parts does not always equal the whole. For example, Bob Goodworker worked 9 hours of overtime this pay period. His budget split will be as follows: Budget A (58.5%) = 5.27 Budget B (31.5%) = 2.84 Budget C (10%) = 0.90 However, 5.27 + 2.84 + .90 = 9.01 The payroll system only allows for numbers up to 2 decimal places.
View Replies!
View Related
Formula  Monthly Budget
I am looking for a formula to sort out a budget problem I am currently working on... I am guessing that the formula is pretty simple but my mind is blank... What I am trying to do is: Cell A1 is my cash budget Cell A3 is actual cash used Cell A5 is The Difference between A1 & A3 (sum A1A3) What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way.. For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required". If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".
View Replies!
View Related
Formula To Work Out A Variance Between Two Times (subtracting Time)
formula to work out a variance between two times Using the 24hr time format in cell a1 i have a start time of 10:43 and in cell b1 i have an estimated time i think a job should take in this case 30 minutes and in cell c1 i have the actual time that job was finished in this case 11:07 and in cell d1 i have a variance between the two times which in this case would be saving me 6 minutes
View Replies!
View Related
Formula: Calculates % Of Budget Based On Signs
I am having an issue creating a formula that calculates % of Budget based on signs. What I mean is positive budget, negative actual and vice versa. Here are all the cases. Can someone please create a formula that I can copy down. Actual Plan% Budget 85,60696,85188.4% 7082185.9% 1,5303,786247% 100100200%
View Replies!
View Related
Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list. how can i calculate the variance? i have the upper limit (=MIN(1,(mean+half width)) i have the lower limit (=MAX(0,meanhalf width) i have the mean (avg of all numbers) i have the t value (TINV(alpha, (n1))) i have the half width (t value * SQRT of Var/N) i just don't know how to get the VAR/N
View Replies!
View Related
Time Variance
Column A has a time (no date) Column B VLooksup a value from a separate sheet per country, so it pulls through a the variance [11 to +13] from UTC (GMT) time dependent on country. All other data is irrelevant. Let's say Column C has following formula: A2+B2/24. This works where the time result (new time) is on the same day, but as soon as it crosses over midnight, it buggers up. What I'm needing to do is take a list of events (server time/GMT) and convert them to the local time from where the event was triggered based on source country.
View Replies!
View Related
Calculating Time Variance
I'm trying to calculate the variance between planned date & time of arrival vs actual date & time of arrival. I attach the workbook as am a bit useless at explaining myself.... What I've done is in H14 subtract the actual date of arrival (F14) from planned date of arrival (C14). This result is the only way I could think of dealing with crossing over midnight. As a result I14 should subtract the actual time of arrival (E14) from planned time of arrival (B14): =SUM(E14B14,H14) This method works well when the arrival was later than expected but doesn't work if the arrival was sooner than expected.
View Replies!
View Related
Message Variance Greater Than 5
I have a workfile with several worksheets. I have the word "variance" appearing in some of the rows in column B and a value in column C which is in the same row as the word "variance" for eg row B19 Variance C19 10 I need VBA code that where the value which is in line with the text "Variance" is greater than 5, the name of the worksheet is listed in the worksheet named "Variances" See Example below ******** ******************** ************************************************************************>Microsoft Excel  Statistical Data.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC13C14C16C18= ABCD131110XFORD*UNITS*IN*STOCK86*141111XMazda*N/V*UNITS*IN*STOCK17*15****16Total*103*17****18*Variance0*East* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
X Condition Lookup And Variance
I need a formula to workout what is the most up to date firmware that should be installed on the machine. This will always be in column B. The second formula should work out the variance between the installed firmware and recomended. The formula cells will be normally held on a different summary sheet.
View Replies!
View Related
Budget Projections
We have hourly amounts remaining on projects, we want to project cost at 40 hour work weeks and 50 hour work weeks. We simply multiply the 40 hour budgets times the straight time rate, but we need to calculate the over time on the 50 hour budgets. examples: 600 hours on 40 hour budget at $25 and hour is $15,000. However on a 50 hour budget it would include 480 straight time hours and 120 overtime hours for a budget of $16,500. Our data is hourly bugets in column "A". In column "B" we did =A3*straighttimerate. Is there a formula that can calculate 40hrs straight time then 10hrs over time until the budget runs out? We are working with Microsoft Office Proffesional 2007 with Excel 2007 but need to remain compatible with Excel 2003. Budget (hours)straight time ($25)over time ($37.5)240$6,000.00?4652$116,300.00?152$3,800.00?258$6,450.00?645$16,125.00?
View Replies!
View Related
Fornula For Displaying Positive Or Negative Variance
I need a formula that will display a 'yes' or 'no' if the following condition is met: If the value of cell (L17) is greater than 10% positive variance, then 'YES' else 'NO'. Currently the value of L17 is 14%, therefore should be a 'YES', however how can I get the following to work: IF(AND(L17=<0,"NO",L17>10%,"YES")) I apologise as I know there are too many arguements here but is there a way around this?
View Replies!
View Related
Red(ve)/Green(+ve) Text For Under/over Monthly Budget
I've devised a monthly savings sheet to allow me to track my savings towards a deposit on a house. My target is £xxx a month, but as I could contribute loose amounts of money I'd like to be able to display a field next to each month of how much over I've contributed or how much under I've contributed. I'd like this to be represented as: If I contribute more than £xxx for a month then display it as green text and calculate how much over has been contributed. If I contribute less than £xxx for a month then display it as red text and calculator how much under the contribution has been. I'm really not sure how to do this, I would do it manually normally but surely there is a way of automatically representing it.
View Replies!
View Related
Divide A Budget Of 100k For Each Of My Employees
how I would be able to divide a budget of 100k for each of my employees. If I have employees making anywhere from 20,446.40  69,659.20 How can I give each employee a portion on these funds? For example if: Lisa makes 20,446.40 Tom  24,448.20 Liz  34,854.60 Mike  44,564.80 Jan  62,061.48 Dan  54,324.20 What would be the precentage that I could give each employee?
View Replies!
View Related
Show Budget VS Actual In Pivot Table
I have weekly budget report (for the entire year) that I put in a Pivot Table with approx 20 different row labels. The actual revenue numbers come only once a month (several different reports make up the actual revenue numbers). I need to put the actual numbers on the same row as the budget numbers and I can't figure it out. For example, for the first three months of the year, I have the pivot table showing everything perfectly. In February, I get January's actual revenue numbers that now "replace" the budget numbers for January. If this was a regular table, I could easily overwrite these numbers, but with the pivot table, I can't get these two fields to line up. For now, I have the Actual Revenue numbers on the Rows above the Budget numbers for their respective months/quarters.
View Replies!
View Related
Text Color Change  Cell By Cell Basis  When Budget Is Reached
I'm a business/application consultant for ERP software, and generally pretty solid when it comes to excel. However, I've come across a question for a personal sheet that I can't seem to solve. In my personal budget worksheet, I'd like to set a budget on a cellbycell basis. When the budget is hit, I'd like the text color (or cell color) to change. I.E. Cell D14 has a budget of $200. When I enter $200 in the cell, the text turns from black to red. I've searched through google with no luck. I have mildly searched this forum, but haven't found my direct answer  I have come pretty close though, in other languages
View Replies!
View Related
Formula For Deleting A Formula When The Formula Has Done Its Calculation
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating. I've got a formula =IF(COUNTA(A1)=1,TODAY(),"") When something is typed in Cell A1 the cell with the formula will input todays date. Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
View Replies!
View Related
Replace Character Used In Formula Where Formula References Cell
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url. i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6 this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
View Replies!
View Related
Combine Formula Which Reduce The No. Of Additoanl Formula's Columns
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass. To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them. Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
View Replies!
View Related
Copy A Formula In A Cell And Then Paste Only The Text Of The Formula
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text. When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formulawise in that the references move.
View Replies!
View Related
Add Addition If Condition To Existing Formula: Long Formula
This task joins a string together based on a number of characters per cell in the range. I want to isolate one range, Col N, and add an IF condition to it. There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View Replies!
View Related
Link Formula / Update Formula Automatically
I’m working on quite huge spreadsheets. The same formula will be used on more than 30 sheets. So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming). I’m wondering if it’s possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas). I can copy the formula with the “Past Special” function but the function is not updating if I change the formula on the main sheet. So is it possible to link it and that it’ll be updated automatically when I’m changing the formula on the main sheet?
View Replies!
View Related
Formula Cell Showing Formula Not Result
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
View Replies!
View Related
Formula's Keep Disappearing Formula Does The Calculation And Then Disappears
I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet... however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information! one such formula is: =IF(AND(AC11"No Calc",AC11>=1,AC11
View Replies!
View Related
Use The Results Of A Formula As Column/row Numbers In Another Formula
I have two cells. The first cell has the formula: =CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0")) which results in a col and row number (such as D65). The second cell has the following formula: =INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2) ^^ I wish to replace the 'D1" in the Match function with the results of the first cell's formula. I assume Indirect would work, but I don't know how to code the formula to use it.
View Replies!
View Related
Offset Formula: Getting N/A In Cell When Enter Formula
I have attached a sample of the spreadsheet. The cell/cells in question are f32:f36 on the first tab. Question #1 how this formula works or what it does. Question #2 Is when I enter the required information in cells a32:e32 it enters an N/A in cell f32. When I than click on cell F32 and than click on the formula bar and hit enter the information populates.
View Replies!
View Related
Date Formula & Add If Statement To The Existing Formula
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
View Replies!
View Related
Can A Macro Write A Formula As An Array Formula
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
View Replies!
View Related
How To Use A Text Formula In A Real Formula
This is going to be hard to explain but, ill give it a try I have a list of formula written in text in columnB (0.0045*x^3+0.2696*x^26.0587*x+100)/100 (0.0045*(x*1.3)^3+0.2696*(x*1.3)^26.0587*(x*1.3)+100)/100 (0.0045*(x*1.5)^3+0.2696*(x*1.5)^26.0587*(x*1.5)+100)/100 (0.0045*(x*0.8)^3+0.2696*(x*0.8)^26.0587*(x*0.8)+100)/100 (0.0045*(x*1.3)^3+0.2696*(x*1.3)^26.0587*(x*1.3)+100)/100 (0.0045*(x*1.5)^3+0.2696*(x*1.5)^26.0587*(x*1.5)+100)/100 each formula correspond to a type of road in columnA Rural Local Rural Collector Rural Arterial Urban Local Urban Collector Urban Arterial I would like to create a formula that will choose the right formula and substitute the variable "x" by a specific cell (lets put $Z$1) to finally give me the final answer in column C.
View Replies!
View Related
Percentage With Formula Or Conditional Formula
I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows: For example, Jamaal is authorized for 80 hours per month but only uses 15 hours. What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance. Therefore, I came to the conclusion that: 0.25% x 80 = 20 hours 0.50% x 80 = 40 hours 0.75% x 80 = 60 hours Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 2550, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.
View Replies!
View Related
