Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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 blank--but 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 A1-A3)

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

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,mean-half width)
i have the mean (avg of all numbers)
i have the t value (TINV(alpha, (n-1)))
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 VLooks-up 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):


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=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

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
Sum Up The Batch Quantities For A Date With Variance One
I need to sum up the batch quantities for a date with variance one...
but it doesn't work... I suspect that I'm using wrong formula, it should be not SUMPRODUCT...

when I tried to use just SUM, it adds all the quantities in the colomn.


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.

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:


I apologise as I know there are too many arguements here but is there a way around this?

View Replies!   View Related
Calculate Variance Between Actual And Forecast Data
what is the formula to calculate variance in Excel between the actual data and the forecasted one?

View Replies!   View Related
Conditional Formatting :: Total Budget
I have a couple of columns in excel and one of them " total budget" and another is "Business Case". How can I do a check on the budget, say if total budget is less than $250,000 then business case value should change to N/A.

View Replies!   View Related
Assistance With Budget Expenditure Forecasting
I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).

I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2

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
Make Positive Entered Numbers Appear As Negative For Budget
I am trying to create a budget in excel and want to type my numbers (in certain cells only) without a - sign but I want them to appear, and calculate, as negative numbers. Basically, I am lazy and don't want to have to remember to use a - for all of my expenses when I am inputting the data.

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 cell-by-cell 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


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 Cell References In Formula With The Header Of The Cells Referenced In Said Formula
Here is the scenario:

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take

and produce:

View Replies!   View Related
Vlookup Formula In VBA - Object Error (input A Formula Into A Cell)
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.

I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6

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 formula-wise in that the references move.

View Replies!   View Related
Formula Arrays That Will Omit Empty Cells In My Formula
I am looking to average a range of cells which won't always be the same size. How do I create a formula array that will omit empty cells in my formula.

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
Not Responding: Added A Formula To A Spreadsheet With Som Complicated Formula
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.

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
SUMIF Formula That Is Collects Values From An IF Formula
Can anyone tell me why this formula is not working?

=SUMIF($D$6:$AL$6,"=TM Goals",D12:AL12)

The entry under cell T12, which I am hoping this formula will pick up is based on the following formula.


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
Cells Display Formula, Not The Results Of The Formula
To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?

Example of formula:
=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))

View Replies!   View Related
Formula To Determine If A Cell Has A Formula Or Constant
In cell A1 exists data. In cell A2, I would like to test A1 to determine if A1 is a formula or a constant.

=A9+3 would result "formula"
="1+3 would result "formula"
3 would result "constant"
'Blahblah would result "constant".

View Replies!   View Related
A Formula Which Is Calculate 4-5 Dates Formula In One Cell
i need a formula for which calculate all cell (calculating years months and days formula). FIND ATTACHED .xls file i need a answer in cell highlited in yellow color.

View Replies!   View Related
Logical Formula: Value If True/false To Be Another Formula, Not Just A Set Value
I want to use the If formula but want the value if true/false to be another formula, not just a set value. Here's what I came up with (obviously wrong, please excuse my lack of experience) =IF(D2>C2,"=(D2-C2)*5","=((60-C2)+D2)*5 ")

View Replies!   View Related
IF Formula Assistance: Formula That Looks At 2 Adjacent Cells
I need to create a formula that looks at 2 adjacent cells. If both cells show 'YES' then I need the 3 cell to show 'YES'. If either/both of the 2 adjacent cells are blank then the third needs to be blank.

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
If(iserror Formula): Used A Variant Of This Formula Before Without Encountering
I'm going wrong with this formula: =if(iserror(J3-VLOOKUP (H3,$H$730:$J$1456,3,FALSE)," "(J3-VLOOKUP(H3,$H$730:$J$1456,3,FALSE)). I feel like I've used a variant of this formula before without encountering any problems.

View Replies!   View Related
Allow Input Into Formula Cell While Retaining Formula
i'm having trouble formating a cell to accept either user input or a equation i've made. i would like it to accept the user input and when there is no input to use the equation i have.

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
IF Formula Does Not Work On The Last Part Of This Formula
This IF formula does not work on the last part of this formula
IF(E10>50,"$50.00",0)))) and I think there is a conflict with the
IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.


View Replies!   View Related
Code Formula So The Formula Is Not In The Cell
I am using the Networkdays function to calculate the number of working days between a date entered in A1 and a date in B2 and I have a range of holidays called 'holidays' and then have the result placed in E1.

View Replies!   View Related
Insert The Rounddown Formula Into This Formula
I use this formula for converting date of birth to a number based on today’s date. I have tried to have the number.. 6yrs 6 months to print only 6 not 7 years old. How do I insert the rounddown formula into this 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

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 25-50, 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
Copyright © 2005-08, All rights reserved