Tracker
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.

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.

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.

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".

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

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%
70-82185.9%
-1,530-3,786247%
-100100-200%

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

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.

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(E14-B14,H14)

This method works well when the arrival was later than expected but doesn't work if the arrival was sooner than expected.

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.

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.

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.

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?

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?

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

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.

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
>D2),(B2-C2)*F6+F2,IF((B2

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.

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?

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.

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.

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

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.

Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

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

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

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

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

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?

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.

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.

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....

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.

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?

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.

=IF(E12="A",\$L\$5,"0")

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.

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

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.

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," "))

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.

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

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.

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 ")

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.

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.

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?

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.

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.

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?

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.

=IF(E8>0,"\$0.00",IF(E9199,(E9*0.01),IF(E10>50,"\$50.00",0))))

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.

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

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^2-6.0587*x+100)/100
(-0.0045*(x*1.3)^3+0.2696*(x*1.3)^2-6.0587*(x*1.3)+100)/100
(-0.0045*(x*1.5)^3+0.2696*(x*1.5)^2-6.0587*(x*1.5)+100)/100
(-0.0045*(x*0.8)^3+0.2696*(x*0.8)^2-6.0587*(x*0.8)+100)/100
(-0.0045*(x*1.3)^3+0.2696*(x*1.3)^2-6.0587*(x*1.3)+100)/100
(-0.0045*(x*1.5)^3+0.2696*(x*1.5)^2-6.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.

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.