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%
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 2 Replies View RelatedIs there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?
Please see an example:
Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer
[Code] .....
I guess it has something to do with AVG and IF formulas.
I have an excel spreadsheet which calculates the count and value of line items based on a report. I use the sumproduct function to calculate this as it involves multiple criteria. The formula is use is
SUMPRODUCT(('Report 50'!$C$2:$C$64992= Dashboard!$B172)*('Report 50'!$H$2:$H$64992=Dashboard!G$2)*(LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")*('Report 50'!$L$2:$L$64992=15))+SUMPRODUCT(('Report 50'!$C$2:$C$64992=Dashboard!$B172)*('Report 50'!$H$2:$H$64992=Dashboard!G$2)*(LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")*('Report 50'!$L$2:$L$64992=19))
I think the possible error is coming out of this criteria (LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")
Out of one particular field i am picking those line items in which the first 14 characters are "Credit Transfe", if i eliminate this criteria i dont get error.
But this is the main criteria for my calculation. Most of the time i dont get any error , but sometines i get the # Name Error? When i double click on the cell it goes to one column. I dont know what to do from there.
I am trying to create a spreadsheet that will keep track of budget spent and budget remaining.
I need the values to take what is spent from expenses claim forms that are filled out,
Each budget category has a code and within that code there are sub heddings
I need a formula to calculate the sum of a range of rows.
Example:
If rows 1-10 have a width of 12.75 each, and rows 11-15 have a width of 15.5 each. I need a formula to tell me that the sum of the widths of rows 1 to 15 is 205.
Any idea?
The Excel file is in this link
[URL]
If you look at the chart on the left, it breaks down how this whole thing works. It takes the sum of the numbers that fall within each line. I wrote it out on column M so that it's more clear. In column D-L, I filled in random numbers ranging from 4000-700. The numbers in those gray boxes are ignored. This Macro should only apply to Column M and beyond. Is it possible to write a macro that will be able to do this?
I have the following formula:
=TEXT(D2-C2-TIME(,60,),"[h] h
s: m mi
s")
It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?
I'm trying to make a worksheet that calculates monthly residuals. For example, if I have income of $275 in the first month and then the next month I have the first $275 and an additional $275, and so on. I've created the following formula
=RC[-1]+(RC[-1]*2)+(RC[-1]*3)+(RC[-1]*4)+(RC[-1]*5)+(RC[-1]*6)+(RC[-1]*7)+(RC[-1]*8)+(RC[-1]*9)+(RC[-1]*10)+(RC[-1]*11)+(RC[-1]*12)
where RC[-1] is the base amount that adds on every month. This gives me the number I am looking for. However, I am wondering if there is an easier way to do this.
Excel 2003 SP2
--------------
I have been programmatically setting the formulae in a worksheet. I have various formulae:
Example 1 - =COUNTIF(Data!M$6:M$9871,3)/$E$4
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 56.80%
The cell displays 56.80%, all is good!
Example 2 - =SUM(IF(Data!$J$6:$J$9871=$D8,IF(Data!$M$6:$M$9871=3,1,0)))/$E$8
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 53.37%
The cell displays 0.00% !?!
Column C contains zip codes.
Column D contains # of adults in the household.
Column E contains # of children in the household.
How might I create a formula that calculates a total # of adults AND children in Columns D and E based upon a series of specific zip codes (but not all the zip codes) in Column C?
using Office Professional Plus (2013). Within my spreadsheet, I have a series of progressive dates to be used in as a timeline. Ultimately, what I need to accomplish is that when I change one date, all the proceeding entries update according to my timeline criteria (in this case 3 weeks or 21 days). I created a SUM formula that worked but only calculated one time. Once the calculation completes, the cell drops the formula. I need this to be constant as the dates often change. How do I get the formula to maintain? Further more, is there a better method to accomplish the task at hand other than the SUM option?
View 7 Replies View RelatedI need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.
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 2 Replies View RelatedI 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.
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".
I've just seen a formula with two - (minus) signs right next to each other placed before a section in a formula while reading possible answers to my post. Here is the link: Understanding this formula. I've seen it before and am intrigued as to why it is there and what it actually does. Assume it is used such: = --(1+2). Would the answer then not be =--3 = 3?
View 4 Replies View RelatedI have written a code for equal distribution of Budget Data based on condition.
The user will enter annual budget number in Column K (highlighted in Green) and based on condition in Column N (highlighted in Yellow), the data will be divided by 12 and it need to be equally distributed from Colum P to Column AA. The condition for distribution is 'Y' (Yes)/ 'N' (No).
I have also attached the sample file.Budget_Input Form.xlsm
I am working on a budget spreadsheet and want to find a formula that will automatically tell me what my take home pay is depending on the yearly salary and see if covers my expenses (linked to another spreadsheet). It will be easier to budget my expenses depending on my salary. My attempts to create IF and = haven't worked.
View 2 Replies View RelatedI trying to write a conditional formula to test data in a cell to see where it falls between certain values, and depending on the data in the cell, will display a message in another cell showing what level has been attained. It's a long formula and I may not have the syntax correct. Can someone look at this and tell me where I have went wrong? All I get in the target cell is "################".
Here is the formula:
Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
=IF(c13<1600,"Not at prize level yet",IF(AND(c13>=1600,c13<=1999.99),"You have reached level 1",IF(AND(c13>1999.99,c13<=2499.99),"You have reached level 2",IF(AND(c13>2499.99,c13<=2999.99),You have reached level 3,IF(AND(c13>3000,c13<=3499.99),"You have reached level level 4",IF(c13>3500,"You have reached the top level!!"))))))
I am trying to create a document that takes our client PO amounts, tactics/spend line items, and dates from one sheet and averages them across the months that it is eligible, so we can know the total amount of money we can expect to go out from our clients each month.
For example:
Client1 Tactic1 StartDate EndDate TotalAmount
Client1 Tactic2 StartDate EndDate TotalAmount
Client1 Tactic3 StartDate EndDate TotalAmount
Client2 Tactic1 StartDate EndDate TotalAmount
Client2 Tactic2 StartDate EndDate TotalAmount
I have mostly figured this out in a really complicated way with many nested if statements, but there HAS to be a simpler way. There will be lots of hands in this document, so I would like to make it as simple and easy as possible.
I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column. Below, there are two procedures. "SimpleCalc" and "SimpleCalc2". I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it.
I'm trying to develop more function based on this code, so I need to make it neat and flexible. what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"? I'm using Excel 2003.
Sub SimpleCalc()
Dim SimpleMethodRng, SimpleMethod As String, i As Integer
ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select
SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12")
For i = 1 To 8
SimpleMethod = SimpleMethodRng(i, 1).........
Sample stock system LOADTESTING.xlsm has ten products. "LoadTesting" shows the opening stock for each. CSV file "Van Sales" has the Stock Number in Col M and the sales figure in Col N. I want the code to Open a CSV file "Van Sales" (which for some reason the site would not upload) Establish the sum total sold for each product (VBA equivalent of SUMIF) Deduct that sum for each item from the opening numbers on "Van Sales" (e.g. Product 001 started with 156 on the van, 92 were sold, so the "revised" van figure should be 64).
Can't get the code to link to the right "ranges" from the Van Sales workbook.
(You will have to change the file path calling the Van Sales file, but if you do get it to work,
VB:
Option Explicit
Sub GETSALE()
Dim Dest As Range, Dest1 As Range, Dest2 As Range, Dest3 As Range, oCell As Range, varResult As Variant
[Code].....
I have an entire row of if statements that basically check other sheets if a number occurs. If it does then it will display "Pending Approval" ,"Open", or "Closed" depending on which sheet the # appears on .. Just wandering if their was a better formula for this..
View 7 Replies View RelatedI have written an IF formula that only clacluates the first IF values in the formula and I dont know why.
Here is my formula: =IF(I2999;"1000";IF(I2>1999;"2000";IF(I2>2999;"3000";IF(I2>3999;"4000")))))
So if in I2 I have the value 800 it correctly returns
If I have a worksheet that has a figure when the program calculates, how do I set up in another worksheet so that it tells me how many pages to print (that has information based on the 1st spreadsheet).
View 9 Replies View RelatedThe whole document works but the last one i need. I'll post it all just in case i have an error elsewhere.
Start Time -- Stop Time -- Break -- Total -- Hours Worked -- Average -- Pay
6:00 -------- 3:00 ------- Yes ---- 1600 -- 8.00 ----------- 200 ------ $xx.xx
E2 Formula for time: =IF(C2="Yes",(((A2*24+12)-(B2*24+24))+1),((A2*24+12)-(B2*24+24)))
F2 Formula for average: =D2/E2. and now for the problem one. G2 Formula for pay: =IF(F2<165, E2 * 7.25, IF(F2<180, D2 * 0.07, IF(F2<190, D2*0.08, D2*0.09)))
Just to break that formula down more:
0 - 164 = 7.25 * Hours Worked
165 - 179 = 0.07 * Total
180 - 189 = 0.08 * Total
190+ = 0.09 * Total
it only calculates 7.25 by the hours worked no matter what comes up in F2
I am looking to create a "Retractable Menu with the + and - signs". If you don't know what I mean, go to excel help and click on the contents tab - that is the menu system I am talking about.
View 2 Replies View RelatedI have created a macro that searches my drives for files, lists them in excel and turns them into hyperlinks. My problem is that a majority of the names use # signs in the file name. What is the best way of going around this problem without changing the file name complete. There is approximately 100,000 different files in different locations that would need to be alters so I think it would be easier to just make the name link work with the # sign.
View 1 Replies View RelatedI have this autosum macro it does its job very well. Problem is that it returns autosum with absolute cell references ($ signs around cell numbers). This way I cannot copy paste formula around same row but different columns. I would rather have to copy paste code for each column and change declarations.
How to edit this code so that variable cell references are returned in the result.
Code:
Dim cel1 As String, cel2 As String
cel1 = ActiveCell.Offset(-2, 0).End(xlUp).Address
cel2 = ActiveCell.Offset(-1).Address
ActiveCell.Value = "=sum(" & (cel1) & ":" & (cel2) & ")"
