Looks like this is the best place for all my Excel troubles, so...
I want to SUM all the values between two numbers, for example 1 and 6 in 20 cells. For this I have the following array formula:
=SUM((H1:H20>0)*(H1:H20
I can count the blank cells withiin a range using
=COUNTBLANK(C6:AD2506)
But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.
It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.
I want to create a 'capture all' formula that adds up cells in a column This column may contain blank cells in one column but when copied to another column, the same row may contain a value.
example I want the sum of A5 + A8 + A10 + A22 but in column A cell A5 may be blank and so may A22 but when the formula is copied to column B all the cells may contain a value
When I try to do it I receive a Value# error for columns containing blank cells I don't want to do a separate formula for each column
I currently have a spreadsheet that I had to convert from multiple rows to columns:
[URL]
Now I need a script to change the data so that each column is now in row format, (see attached spreadsheet).
I'm migrating data from Financisto for Android to CashTrails for iOS, and I need to format my CSV file a specific way for conversion to a CashTrails file.
I'd like to do the following in Excel 2013 (I have access to other versions if I need to):
1. Highlight a range of cells (i.e. A1-H20)
2. Apply following action: IF cell has data, add " to beginning of cell and " to end of cell, AND IF cell has no data, place "" in cell.
This can be done, right? I'm desperate!
It would look like this in Notepad.
Before:
Date,Time,Amount,Category,Tags,Account
12/31/13,12:00 PM,$3.99,General,,Checking
12/31/13,12:30 PM,$5.00,Shopping,gift,Cash
After:
"Date","Time","Amount","Category","Tags","Account"
"12/31/13","12:00 PM","$34.99","General","","Checking"
"12/31/13","12:30 PM","$5.00","Shopping","gift","Cash"
I found this great macro to use in a spreadsheet I'm trying to transpose from rows to columns, however, I need a place holder for blank cells: [URL] .....
I was able to use the macro in the last post by Ochenden but the blank cells need to have a placeholder.
How to change the macro or come up with a different script for me to use?
Attached is how I need the spreadsheet to look.
Script I used:
Sub aaa()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet2")
Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=OutSH.Range("A1"), Unique:=xlYes
[Code] ..........
Transpose_Duplicates_Examples.xlsx
How wud I find out the total number of rows in a column, that are non blank? Value "False" and N/A are considered as blank cell , but a cell with --> one or more space " " is allowed.
View 5 Replies View RelatedI am trying to create a SUMIFS function to sum estimated hours for specific systems that do not have a Completed date. I do not want to sum remaining hours if it has been completed. i.e.
=SUMIFS(Hours, System#,"110-000-00, Complete (Date), ISBLANK)
System #
HOURS
Complete (Date)
110-000-00
0.5
210-000-00
0.5
[Code] .........
I have a row of cells containing numbers, some of which are 0, how can i get an average where it averages all cells except the 0, as at the moment it is distorting my results.
View 3 Replies View Related1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
I need to count the number of entries made in an column provided it should exclude text values but it should count all the values which is combined of text and numbers ..... I tried count but it's only counting cells containing number....
View 7 Replies View RelatedI have been trying to import CSV files into Excel on my new computer. I previously had a windows 7 system with office 2007. I have now moved across to a windows 8 system with office 2013. The problem I am faced with is the CSV files which I imported without problems on my old system don't seem to work on the new one. The excel spreadsheet shows the numbers, but when I try and use any formula, the cell comes up with 0, as if there is nothing in the cell. When using my old system, it shows that it is a picture that needs to be converted to text, but I cannot find out how to sort this out in Windows 8. I have even tried various versions of office, 2007, and 2010 on the windows 8 system with the same problem. I have tried all the things I have found online, checking the regional settings and those type of things.
View 10 Replies View RelatedIf you have figures in 4 cells, i.e. A1, H1, A10, H10 and blanks in all cells within that array, is there a function which fills in numbers in the blank cells?
I have an Excel workbook with around 100 worksheets. All of them have similar columns, but the values are stored as text. The length of columns and missingness is different in different worksheets. I will be grateful if any of you could suggest a VBA that could convert the numbers stored as text back to numbers. I have been looking for an answer for a while, but cant seem to find one that automatically looks at every worksheet and converts text into number.
View 1 Replies View RelatedMy spreadsheet is multiple rows and columns of sales data with two blank rows between salespeople. Ex:
$100 ABC Inc. John #101
$150 XYZ Inc. John #101
$200 Golf Inc. Sue #102
$150 BBall Inc. Sue #102
What I need is this:
$100 ABC Inc. John #101
$150 XYZ Inc. John #101
$250
$200 Golf Inc. Sue #102
$150 BBall Inc. Sue #102
$350
Where the sum of each salesperson's sales figures is shown and formatted. I've been doing this manually. I'm having difficulty figuring out how to do this using VBA. I can do the formatting, and conceptually I see what needs to be done.
I have the following macro:-
ActiveSheet.Select
ActiveSheet.Copy
Columns("A:D").EntireColumn.AutoFit
Range("A2:B500").Select
Selection.Sort Key1:=Range("B2:B500"), order1:=xlAscending, Key2:=Range("A6:A250"), order2:=xlAscending
Within this I would like to add a couple of features which check:-
1) If there is a number in column A - there should be one in column B
2) The numbers in column B are sequential i.e. 1, 2, 3 etc without any gaps in the sequence
a spreadsheet in Excel. I have names with scores. Then I have the winning score. I need a formula to find the score closest to zero and to display the name of the winner.
Ex: Names A1:A4 and Scores B1:B4. Winning Score in B6 and list name in B7.
Ana 16
Bob 2
Charles 8
David 11
Winning Score 10
Answer should be 11 which is David, since David is only -1 away compared to the others.
Here is the formula I am using however it interprets the blank cells as 0% and includes it in the average. To make it more difficult there will be some 0%'s.
View 4 Replies View RelatedI'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
I have an excel file and what i want is to sum two different columns to a third one, however there are some important notes:
1. Column A has dates
2. Column B has numbers f.e. 25, 30, 35 etc. , which have to be added to dates of column 1 , so a new date will be created in the new column
3. Colums A and B are not next to each other.
4. Some cells of column A and column B are blank , actually columns are sth like this
A B C
5/2/2014 25
blank blank
8/5/2014 35
10/9/2014 30
blank blank
When a date value occurs in a cell of column A , then always a number will occur in column B
When a cell is blank is column A , then the respective cell of column B will also be blank
What i want is to add values of column A and B to create column C automatically ( each column has 1000 cells ) , however in case of blank cells , i want a blank cell in column C, not something like #####
Apart from this , i want in the future , when i expand column A and B , when values are entered there, i want column C to be created automatically.
s/s is 325501 rows deep. Down one column are values but these are seperated by blank cells.
I need to omit the blank cells so that all the values appear together, but keep them in the same formation (not sorted).
I have a column which contains unique values and also blank cells between them. These blank cells are associated with the cells having value below them. e.g.
Say we have
COLUMN A
1004Z
blank cell
blank cell
blank cell
blank cell
1031Z
1305Z
1007Z
1046Z
blank cell
blank cell
1400Z
blank cell
1021Z
Suppose these above are 14 cells (A1:A14); I want to merge preceding set of blank cells with the values below them.... in this example I want to merge A2,A3,A4,A5 with value in A6...
Likewise A10,A11 merge with A12 to show value in A12.
I have only one column to merge values like this.
s/s is 325501 rows deep. Down one column are values but these are seperated by blank cells. I need to omit the blank cells so that all the values appear together, but keep them in the same formation (not sorted).
View 4 Replies View RelatedI want to show in tab total view the advices for problems which come from a questionnaire. This questionnaire applies to its advices on the basis of given questions of the user. In the advice tab you can see the advice given for each question. Because I have shown in the advice tab in column B if the advice need be in the tab total overview, if this is not the case there is nothing visible there (empty cel). In column C indicated by 1 if the text should be to the overview tab and a 0 if the specified text should not be taken to the tab overview. How can I get in the overview tab only the text from column B which have in column C the value 1 (without the empty cels)? Even when the data in tab advice changes i want that the information in the tab overview also changes.
View 3 Replies View RelatedA1:A1600 contain either random values or blank cells.
Each time there is a blank, I would like a fresh count placed next to the blank cell in B which counts the populated rows between each blank.
My problem is that I have no idea how to set my count back to zero each time I hit a blank and continue down my column. There is no consistancy between blanks so I need the flexability.
Example
A B
1 14
2 998 2
3
4 3
5 8
6 22 3
7
I have a scenario where I have a rolling list of sales figures which get added to each week that passes.
I need a formula that will calculate the last 5 weeks of sales and generate an average - which I think I have an idea how to do.
The sticking point is that so as not to skew the averages, when there has been an exceptionally busy or quiet day for a reason we know about I exclude the sales from that week.
This then interferes with the averages as it either takes it as a zero and lowers it or seems to stop formulas from working.
So to summarise:
Average of last 5 weeks sales
Excluding any blanks
Dynamic enough to always pick up the last 5 values in the list (i.e. the last 5 weeks)
I have created a drop down cell based on the cells in the first row of a particular table. Now when i select a particular cell from the drop down i want to fetch all the values in the respective column of the selected cell on to a different group of cells. While fetching the data to those group of cells, i want to ignore all the null, blank cells. All this actions shall be automated and shall happen simultaneously on selecting the drop down.
View 1 Replies View Relatedi have a list of 100 product codes in row b2:cw2, colum A contains dates in the month, yesterdays first and then each previous day before that going down, and the columns B:CW are the number of each particular product sold on that day.
I'd like to insert a formula in row 1 which will look down each column and count the number of blank cells from B3 to the first cell to contain a value, i.e. the number of days since this particular product was sold.
Ok so my project is tracking how many days have passed since a collection notice has been sent. Assuming the case isn't closed, a response from the customer hasn't been received, the case has been assigned to an employee, and assuming the notice was actually sent in the first place.
There are instances where the collections case may have been closed without a response date or any other date and we call that "Administratively Closed."
This is my formula: =IF(OR(CaseStatus="Closed",ISBLANK(NoticeSentDate), ResponseDate""),"",TODAY()-NoticeSentDate)
Problem is, now that I'm trying to conditionally format the ones >60 days, and =45 days WITHOUT getting the "blank" cells to change color too.
I got the one for >60 days: =AND(OR(CaseStatus"Closed", CaseStatus"Unassigned"), Comments "Administratively Closed", DaysPassed>60, ResponseDate="", DateEnteredInDatabase"")
I can't get =45 without having a bunch of cells that appear blank change color too.
The source data is collected using drop-down menus. In moving the data from one workbook to another, the drop-down lists are carried over into the destination cells. I have used both: paste special "values" and paste special "valuesandnumber formats". The code in the else statement produces the same result as the 2 step process above it.
View 1 Replies View Related