I am trying to write a function that sums the values in a range in excess of 2. For example, if the cell is 3 add 1, if it is 5 add 3 etc.
Here is what i have so far, it compiles with no bugs, but it does not work in my worksheet. I wrote it in Sheet 1 (VBA) of the worksheet i am using it in.
Need to write a formula which sums the values in a range only to the date we mentioned. For ease sample date & requirements are highlighted in attached spreadsheet.
View 3 Replies View RelatedI have one cell that brings up a random number using RANDBETWEEN and I want to keep a running total in a separate cell, but I can't figure out a formula or function to do this.
View 6 Replies View RelatedI am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View 9 Replies View RelatedThe question is pretty much described in the post title. Here is the situation:
I have a workbook that has data plugged into it manually throughout many sheets, and at the end there are a few sheets with different graphs and charts that are generated by a macro. This macros works perfectly - all the data is accurate and the figures are perfect when compared with a manual count. The problem I have is that the cells where the macro puts the summed values are in a chart that is supposed to create a bar graph. Normally, if I change a value that a bar graph uses I see the bar graph change - but that does not happen with the cells that have values placed in them by the macro. I also have a section that sums the values in the chart to make sure it's running right, and these values do not update automatically as well. The cells have the formula =SUM(B3:B12) and so on, in them. Right now, every time that I run the macro I need to go into the cell with the formula, hit 'enter', and then it does the summation. Is there a way to correct this problem, or is this something that is part of excel?
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
I am looking for a formula that searches a range of data and then returns the sum of the numeric values in the adjacent cells. I have a calendar style spreadsheet with 2 columns for each day. In the first column it has a list of names and the second column a number.
I would like a cell to return the sum of the numeric values next to each name. All the names are entered into the columns randomly and are unorganised. The same name will appear multiple times in the range and have different numbers next to it each time. I am currently using (for example) =COUNTIF(H6:AL14,"bob") to tally up the number of "bobs" but really what i want to do is find a formula that in plain english does:
Search a range of columns and where cell = "bob" take the value of the adjacent cell (to the right) and total them up!
i have attached an image - i hope it makes sense what i'm trying to do - i would like Q3 to total up the numbers to the right of all "bobs" across all the days - so it should return 42
I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:
Counter
Column 2
Column 3
[Code]....
Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.
What I would like it to do is if there is a blank value in any column remove column one from the denominator.
Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.
Column A includes a name
Column B includes an age
Column C states a division (this is the column that contains the Vlookup function)
Column G is where I have to put a range of ages (for instance, x=<0,>3)
Column H is where the division name is.
The idea behind this exercise is to use the Vlookup function in column C to reference column G, and return a value in column H. Here a copy of my formula in column C: =VLOOKUP(B3,$G$3:$H$12,2,TRUE). For instance, if Mike (A) is 16 (B) then he would be in the Midget division (C). C is calculated by comparing column B to G, then returning a value in column H. For some odd reason, I can't get column G to reference an age range, such as 1-5 years old.
I am trying to use the INDIRECT function to look up values on a range of other worksheets. I have a column of data in col A which is essentially a lot of different worksheet names. On each worksheet I need to use VLOOKUP to find a value.
Easier to show formulas. This is an example of what I want to recreate:
=VLOOKUP(O2, test!B3:C13,2,FALSE)
I want to replace the text "test" with the text in column A. So the first INDIRECT formula looks like this:
=INDIRECT("VLOOKUP(O2, " &A1& "!B3:C13,2,FALSE)")
Which to me looks like it should work but I get a #REF! error. I've tried various permutations, e.g. having the INDIRECT part of the formula located in a different place (next to the "test" text) but run in to similar issues.
Is it possible to utilize the AVERAGEIF function for a specific range of values. For Example, instead of ">45", I would be looking to only average the cells that have values between 40 & 60. I've tried several different ways to input that into the function but nothing seems to work.
View 3 Replies View RelatedI've got a spreadsheet that uses about 2200 rows and about 15 columns. However, when I try to insert a column, it freezes up the application. My assumption is that this is caused by the fact that there are in excess of 65,000 rows. When I try to highlight those 63,000 rows and delete them, the system freezes up as well.
These excess rows are unnecessary and I'm sure add to the file size.
Running Windows XP with Excel 2003.
I have a large dataset with 21 columns and 3000 rows, but to keep it simple, the data looks more or less like the first dataset in the attached excel file.
This results in 3 curves (time vs force). However, there is excess data in there, I only need data as soon as the Force becomes positive (>0) untill the Force drops below 0.02 again (I consider that the end-point of my curve). So, this dataset would look like the second set in the excel file after the macro-manipulation.
I think one of the difficulties is making sure that the macro doesn't cut of my data right in the beginning, when the curve first crosses 0.02. In that case, you get the last dataset. And this is not what I want...
I hope I explained my problem well, if not, please let me know.
So, is there anyone who knows how to do this?
By the way, is there anyway to insert small excel tables in this post? Cause I wasn't planning on having an attachment, but if I copy-paste the data in this post, than the formatting gets undone and it looks like there's no way to fix it...
I've exported some data from our finance system into Excel and now need to pivot it, problem is that there are 100k plus rows and therefore goes across more than 1 worksheet. Had a read through the forum and a number of topics about this but I'm a bit stuck. I've got as far as
1. Ungroup the 2 sheets, copied the row headers from sheet 1 to sheet 2 and then named the data in each sheet. I therefore have 2 identically arranged data ranges (albeit one is 65k rows the other 40k rows) named 'block1' and 'block2'
2. In a new workbook I've done
Data > Import External Data > New Database Query
3. I followed the wizard and slected Block1 as the table
4. In MSQuery I've edited the SQL to read
SELECT * from block1 union all select * from block2
I'm now stuck and have 2 issues
1 I'm only showing 8192 records is this a limitaion of MSQuery or has something gone wrong ?
2. I still don't know how I pivot the data
i'm working with downloading data from our server. it downloads in excel 3.0 format, and the contents exceed the 65k cap by far.
now what i used to do for this when i had 2003 installed on my machine, i would open the 3.0 format file and convert it to 2003 format so i could import it into access, where i could move it into two 2003 xls files to manage by use of copy/paste from access's table to excel's spreadsheet. The 3.0 > 2003 conversion was necessary because if i try to import a 3.0 excel file into access 2003, it gives me a error that the file contains no data.
so here is my problem now. our office just updated to 2007. it appears that when you run the conversion option to 2007 format, OR just save as... and save it to a new xlsx, it stripps off the remaining data that overstacks the 3.0 format file. everything above row 65536 is lost (and this paticular file has 77k+ rows).
so i decided... "ok then i'll do it my old way". i saved it into 2003 format instead, just as i used to, and imported it into access, and guess what? it stripps it in that format too now! (the majesty of upgrades... buggs to year 2010).
so my question is... how do i get this 3.0 overstacked file format converted to xlsx format retaining ALL 77k rows in a single sheet, or how do i strip it into 2 separate xls 2003 format files? either way will work, i just need all my data so i can work with it.
I have got a cell or cells with certain number of Characters (Alphabetical or Numeric or Alphanumeric). I would like to remove the excess Characters in the Cell from the end.
Example: If a cell contains 234 characters, Excess characters (More than 200) to be removed from the end.
Any formula or Vba program in generic form, so that i can limit the number of Characters to as required.
I need VBA to remove more than 3 blank spaces after the city & replace them with a comma & one blank. For example:
All in one column AB starting with row 7 I have:
Johnson City (13 Blank Spaces) NC 75503 (1 to 3 spaces after state & is OK)
Jacksonville (10 blank spaces) Fl 23854
Henderson (12 blank spaces) NH 28363 (12 blank spaces)
etc
I need all in one column AC starting with row 7:
Johnson City, NC 75503 (,space before state, same space after state ok)
Jacksonville, FL 23854
Henderson, NH 28363
etc
I have obtained the following data which I need to put into a spreadsheet to import into an accounts program. I can't change the way I get the data which is as follow
Hrs worked Rate
05:55:00£30.00/Hour
07:40:00£21.00/Hour
05:45:00£30.00/Hour
What I need to have is 5.92 30.00
7.67 21.00
5.75 30.00 ie the time format in decimal and loose all the unnecessary symbols etc for the rate
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies View RelatedI'm looking to combine a number of worksheets into a summary sheet .. but on some the narrative is more than the 255 characters. How can I , where this is the case, insert a row below.. say input "Continued" in column A and then copy the contents of the cell which is more than 255 to the cell immediately below it.. and maybe do this again where users input more than 510 characters.
Alternatively, is there a way of getting around the copy and paste values.. where it only inputs 255 ?
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedI have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
Deciding to try and get to grips with Excel for basic accounting, I'd just like to check some things before I start filling columns... Say in column D I have a list of names, and in column E I have a list of figures: John Smith £250 Harry Davis £350 John Smith £500 What would be the formula for finding all occurrences of John Smith, and adding up John Smith's figures to give a total? In the simple case above, the answer would be £750. Would it matter if there are any empty/blank rows in the list?
View 9 Replies View RelatedI have the following table:
Month / Year / Value
JAN20060,73
FEB20060,76
MAR20060,76
APR20060,76
MAY20060,73
JUN20060,69
JUL20060,69
AUG20060,73
SEP20060,76
OCT20060,75
NOV20060,74
DEC20060,72
JAN20070,67
FEB20070,68
MAR20070,71
APR20070,75
MAY20070,75
JUN20070,74
JUL20070,71
AUG20070,68
SEP20070,67
OCT20070,7
NOV20070,73
DEC20070,75
I want excel to multiple the values in a specific range.
For example:
I will give 2 dates: MAR 2006 and MAR 2007. Excel should multiple the value 0,76 and 0,71. It's simples because is 2 months. But I have a lot of months (2006 until 2009).
So I would give MAR 2006 and MAR 2009 and excel should multiple the values from MAR 2006, 2007, 2008 and 2009. All the values in the range, but only for March.
The month will never change between the first and the last date.
I think on using VLOOKUP. Then I have the first value and the last one. But how can I tell excel to multiple the range?
The ideal will be a VLOOKUP that returns the ADDRESS of the cell, not the value. But I didn't see this possibility.
I have a list of tenants.
Column A is the building number
Column B is the Area in metres squared
Column C is the tenant name
What I want to do is sum the vacancy (in sqm) for each building.
Ie, look at column A and choose the rows relating to a particular building, whose number is in column D1
Then look just at those rows and select those tenants named as "vacant"
Then add the areas from column B of those vacant plots.
In basic terms I have column A containing a list of dates, starting from 01/01/2005 and increasing by 1 day for each row, In column B I have the value for the day. These dates and values are still being used so the number of rows will increase day by day. I would like a formula to tell me the average total for January. So it would need to SUM each January before giving the average. I realise I could prob do this with a pivot table but if someone could give me a hint for a formula that would be great.
View 2 Replies View RelatedI have a number in a cell, lets say its 900. I want to multiply this number by 12 and then divide that by 52.
So a calculator I would simply type in 900 x 12 / 52 = 207.7
On excel I tried:
=sum(a1*12) /12
and it didn't work....
I am trying to create a macro that will sum the total number of 1's 2's 3's 4's 5's '6s in a range of cells d17:100 and return the number of 1s to cell a3 and number of two's to cell a4 and number of 3s to cell a5 and so forth.
I also need this to run each time any changes to any cell on that particular worksheet is made - sort of like 'refresh all the sums' type of thing.
I have been working on this spreadsheet for weeks and can't get past this part!
I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.
View 9 Replies View Related