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?
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 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%
Is 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 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.
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.
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
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 one table with 0.5" cell widths. Good.
I have another table with 1.875" cell widths. Good.
I want both tables on the same page, one under the other. Hard.
How can I achieve this? Is there a way to have the column widths of one table independent of the other on the same page? Is there a workaround that would achieve the same effect?
I am doing a if else statement but I do not whats the keyword for getting the column width value
Eg
if column width value = xx
column width value = xxo
I have a column that calculates & displays the number of minutes between two cells, i would like to conditonal format the column to either display a 2-color scale by number, min 0 and max 40 or i would like to apply a fill to all cells > 40. I can do this with normal numbers typed into the cells but i cannot get it to work with the result of the formula. I have tried formating the cells to number but still i cannot get it to work.
View 3 Replies View RelatedI have made a spread sheet where the row 1 - 20 are 12 wide but I'd like rows 20-25 to be a lot wider. I've looked around and can't find the solution since I don't know how to explain it in a few short words.
View 1 Replies View RelatedThis is doing ALMOST everything I need it to do. The only thing I need is for it to keep the formatting and column widths from the source sheet.
[Code].....
I can automate chart creation in VBA, but is there any way to also have it such that the x-axis of the chart begins with the minimum x-value and ends with the maximum x-value in the data set?
View 2 Replies View Relatedhow can know how is my column width in CM ,
View 9 Replies View RelatedI have a 3 column listbox on a userform. I am trying to line the columns up with some label headers on the user form.
On thsi listbox, how can I code the first column to a width of 250, the second column to a width of 50 and the third column to a width of 75?
I would like to know if it's possible to change width of different cells in a single column? Example:
Width of cell A1 would be 10, and width of cell A2 would be 15. Can it be done? If yes how, if no damn. The reason I ask is because I trying to fit a report on one page with a chart and a month worth of data, but some cells are to long and they get cut off.
I've used the macro recorder to try to auto-apply column widths after I do a CSV import:
(A&B=3.14, C=8, D=13.57, E=24.14, F=9, G=10, H=11.29, I=8.57, J=6.86, K=8, L=13, M=10)
...but for some reason when I execute this macro, "every" column gets the width of 10!
how I can fix the below code?
Sub SetColumnWidth()
Columns("A:B").Select
Selection.ColumnWidth = 3.14
Columns("C:C").Select
Selection.ColumnWidth = 8
Columns("D:D").Select
Selection.ColumnWidth = 13.57
Columns("E:E").Select
Selection.ColumnWidth = 24.14
I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
View 3 Replies View RelatedI have lots of files I need to read every day. I need to read them in as fixed width - EX: column A is 1-8, column B is 9-12, column C is 13-22, etc. Is there a way to create a VBA macro or something so that I don't have to point and click to draw lines every day when I read in these files?
View 9 Replies View RelatedI am trying to create a userform that is reusable by turning on and off diff objects, and reusing objects. I am running into a little difficulty of resizing the list box for various lengths if items Example would be if I have a list of items and the longest one is 93 chars long, I need a width of 672 When I have a list that the items are each 5 chars long, I need a listbox width of 92
For simplicity's sake, I am using Courier (supposed to be a monospaced font) pitch is 10. I would have figured that my width would be simply a matter of finding the longest length in the list and then multiply that times some magical number that represents the width of the letter (since monospaced, each letter should be the same).
But, with the 93 char long string, the "average length" of each letter seems to be 7.2 (672/93 = ~7.2) but for the shorter words, it seems to be wider at 18 (92/5 = ~18)
Is there a way to have the listbox size itself to the width of the strings?
Is there any way to measure the width of text in a cell in points? I know that the width of the cell is returned using the Width property.
I also know that the number of characters in the text can be returned using the LEN() function, but I don't believe the number of characters can be converted to points (because some characters are different widths, if I'm not mistaken)