How To Add Positive Only Values From Filtered Results
Feb 22, 2012
Based on filtered data I want to produce a result for the Average trade gain.
Lets say I have 100 rows of data, and based on filtered settings the rows reduce to 5 rows of results.
Of those results I want to find an average of all the positive values (trading profits) and ignore the negative ones.
$2000
$1500
-$700
$4000
-$1000
The answer should be (2000+1500+4000)/3
The need for filtering makes this problematic. I am inclined to use SUBTOTAL as it applies to filters rows only, but I can't see how to use an IF statement within it.
In addition to this I would also love to know how to subtotal all positive values without averaging.
View 5 Replies
ADVERTISEMENT
Apr 9, 2008
Two columns contain data (cash amount) which should be the same. If there is a difference, I need to show a minus value in a different cell and a plus value in another cell.
View 4 Replies
View Related
Feb 2, 2009
I have a monthly work phone bill which I receive as an Excel document. I have to pay for personal calls and want to create a list of personal numbers which can be filtered and the costs summed. Prefereably an add in would be best. Possibly 2 icons, one for adding my personalised numbers (usually under 10 numbers). This could be saved to my home drive on the network so I can run the formula from any PC on the network.
The other icon would be to run the report and do the calculations. So it only really looks in 2 column (mobile num, cost).
View 14 Replies
View Related
Sep 18, 2009
How do I rank filtered results? I do not wish to use VBA if possible.
View 4 Replies
View Related
Oct 20, 2007
I am trying to sum the results of a filter via VBA but cannot seem to get it right. I have the code to count the number of "active" rows, now to get an average I have been trying to use similar code to get the sum so I can divide the sum by the number of rows not filtered out. I get the error "Unable to get the Sum property of the WorkSheet function class" This is the code I have butchered:
Set R = ActiveSheet. Range("d1"). CurrentRegion
Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count)
For i = 1 To R.Rows.Count
If Not R.Rows(i).EntireRow.Hidden Then
intCountCancelled = WorksheetFunction.Sum("Cancelled") ' cancelled is a named range. This is the line its failing on
End If
Next i
View 5 Replies
View Related
Nov 10, 2006
I have a list of data entries. I use auto-filter, to view them according to which category they belongs to. However, when I wanted to sum just the quantity associated with the criteria which I am filtering, I couldnt find a formula which does that
View 2 Replies
View Related
Oct 16, 2007
I have a large data set (from columns A - I, with over 10,000 rows) of information located on Sheet1 that I need to be able to go through to find the criteria (which is text and is located in column B) I'm looking for. I know how to write the VBA code to use Excel's AutoFilter option .... what I don't know is how can I can identify and copy the results the AutoFilter pulls up, from Sheet1 into another sheet because the data is on a number of different rows.
For example, I have to search column B three separate times for the following criteria:
1. xyz
2. acb
3. hij
this is what I have for the autofilter:
Range("A1:I1").AutoFilter Field:=3, Criteria1:="=xyz"
Today, I may find the "xyz" information on rows 6-150, 755-787, 1021, and 8524-8999, whereas tomorrow "xyz" may be on rows 51-101, 8547, and 9989-9991.
View 7 Replies
View Related
Feb 21, 2008
I am populating a 2nd temporary worksheet from a primary using autofilter which works just fine. I have been asked to set it up to populate the 2nd sheet using the same autofilter criteria across two or more of the primary sheet columns. I have been looking through the forums and found an article in which this process is described, #2 The power of Autofilter in VBA - Part 2, and I would like to ask if this will do the job for me.
I did attempt to create my own code, but it didn't work with a loop that I created. I do use input boxes to gather criteria from the user. I have put a copy of my code below which doesn't work, but need to know if the code in the article mentioned above will work?
Option Explicit
Private Sub UserForm_Initialize()
On Error Resume Next
' Dim rheadings, cl As Range
'
' Set rheadings = Worksheets("CONTACT").Range("A1:F1")
' For Each cl In rheadings
' Me.cbxSearchWhere.AddItem cl.Value
' Next cl
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _ ................
View 4 Replies
View Related
Jul 31, 2008
I have designed a Macro to use an Advanced Filter to search for specific criteria in different columns. I would like the results to bold or highlight the cell if it meets the criteria for the advanced filter. There are multiple numbers in each row that are associated with each record, and I would like to be able to see which of these triggered the filter to work for that row by bolding or highlighting the individual cell.
View 8 Replies
View Related
Aug 20, 2006
I have a folder with 250 files. Each of the file has only one column. I need to search the rows starting with "Party Name" and copy them in any one row. I want a macro either to copy the filtered results in the same file or a fresh workbook.
Column A
row 1 ABCDE
row 2 FGHI
row 3 Party Name:Abcd
row 4 JKLM
row 5 nopq
row 6 STUV
row 7 Party Name:ryz
row 8 Party Name:mno
row 9 XYZ
I want the results as below:
Column A Column B Column C Column D
row 1 ABCDE Party Name:Abcd Party Name:ryz Party Name:mno
View 9 Replies
View Related
Sep 2, 2006
Trying to paste filtered results except header row. My code below pastes the header row for "Details" 2x. I tried altering the row to row 2, but that caused incorrect results to be returned.
Detail - Sales
First I filter the data range for " Total Sales"
Paste the results with header row to wsDetails
Details - Details
Then filter the data again for "N"
I would like to paste these results to wsDetails w/o the header
Option Explicit
Sub comp_pl_ytd_Totals()
Dim wbBook As Workbook
Dim wsData As Worksheet
Dim wsTotals As Worksheet
Dim wsDetails As Worksheet
Dim wsExtract As Worksheet
Dim rngData As Range
Dim rngCrit As Range
Dim rngDest As Range
Dim arrCrit
Dim myRange As Range
Dim C As Range
Dim lngrows As Long
Dim strFormula As String
Dim rngCase As Range
With Application
. ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With................
View 4 Replies
View Related
May 15, 2014
I have a sheet of data that I can filter (e.g. only show male pupils).
On the next sheet I want to display the list of filtered results with no gaps.
As the user will be able to select which column of data to show from the first sheet I am referencing it as follows:
INDIRECT("'Progress Matrix'!"&ADDRESS(MATCH($B$6, 'Progress Matrix'!$A:$A, 0)+1,
MATCH(C$6, 'Progress Matrix'!$1:$1, 0))&":"&ADDRESS(300, MATCH(C$6, 'Progress Matrix'!$1:$1, 0))))
(However this formula no longer seems to work as I think I changed something).
I've tried to incorporate
SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))
to only show filtered results but with no success.
Progress Tracker.xlsm
View 6 Replies
View Related
Jan 5, 2014
Excel 2010 windows 8
I am in need of a formula to count filtered results. On Sheet1 (Job Flow) the user enters data continually . The most important is the Date in column D some measurements in columns N,O & P that gives a result as a code, example s15020 or c3005 in column Q. A vlookup is then pulls up a price for the code.
On sheet two Column A There is a list of all the codes. I need a formula that will give a count in column B for the amount of codes that was entered in to the data base "sold" for a date filtered time period, be it a week, month or year. In other words the codes in ,column A sheet 2, should reflect the amount of units for the time filter on page1.
View 1 Replies
View Related
Aug 22, 2006
I have a vertical list of data which is sorted in to "Regions" 1 to 7 there could be up to 25 instances of each Region, but I have no way of knowing.
I want to be able to select each Region and place it in it's own column.
View 9 Replies
View Related
Aug 26, 2009
I have a long list of delegates attending functions on different dates and need help with 2 problems:
A) List has filters by date/venue etc but I will want the table to be visible even when list is filtered.
B) I am using the following formula to count "=SUMPRODUCT(--($E$3:$E$728="Thursday 3rd September 2009"),SUBTOTAL(3,OFFSET($E$3,ROW($E$3:$E728)-MIN(ROW($E$3:$E$728)),,1)))" and this works but if I filter to another date then 3rd September shows 0.
View 6 Replies
View Related
Nov 6, 2012
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
View 4 Replies
View Related
Jan 2, 2014
I'm using Excel 2010. I have a spread sheet with sales data covering 3 years and multiple customers. I was able to create formulas such as this to calculate the figures for the entire sheet (all customers) by year.
=SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13").
I believe that in order to use filters that show this date for a specific customer I need to convert this to a SUMPRODUCT formula, I've tried this multiple times and had no luck.
View 13 Replies
View Related
Apr 7, 2008
I have a table (column C5 to H20)
The table has values in each cell. Some cells have zero values and some have positive values.
Basically, I need to convert all positive (non zero) values to Negative values using VBA (i.e. convert 251 to -251).
View 9 Replies
View Related
Jul 22, 2009
I want cell D5 to be the minimum value of cells D29:T29. Some of the cells will have no value (or $0.00) and some will have values in them. I tried the following formula in cell D5 but it doesn't work: =IF(D29:T29>0,MIN(D29:T29))).
View 8 Replies
View Related
Sep 3, 2009
way using vba to match a -negative value to a postive value in a same column and delete the entire row? (debits and credits)
View 9 Replies
View Related
Mar 7, 2014
Using AVERAGEIF on a row containing both positive and negative values one can easily calculate the Average of only the positive or negative values. I need to filter the 20% outlier on each case, how can I do that with TRIMMEAN ?
This is an example of the current formula using Averageif for the positive values:
=IF(C3=0;0,0001;AVERAGEIF(Auxiliary!B29:BL29;">="&O3))
For now O3 can assumed to be 0.
View 8 Replies
View Related
Mar 15, 2011
I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?
View 6 Replies
View Related
Mar 21, 2014
I'm trying to define a range of values in two cells M1:M2; lookup that range of values in a three column array (K12:M600) and sum the values in column M12:M600 if they are positive values. I've been trying this formula:
=SUMIF(M12:M600,">0",(VLOOKUP(M1:M2,K12:M600,3,TRUE)))
but cannot seem to get it to work.
View 5 Replies
View Related
Jan 24, 2014
I've got 12 months worth of business accounts which have been downloaded onto a single spreadsheet.
These show negative and positive values. As I only want to work on the negative figs at the moment, how do I highlight the colour of the positive value cells or font to prevent any mistakes ?
View 1 Replies
View Related
Apr 13, 2014
I have a sheet which contains credits and debits for several accounts:
Customer
Amount
ABC
2000
XYZ
3000
XYZ
1000
ABC
1000
ABC
-5000
XYZ
1000
What I need to do is match negative and positive values within each account and highlight them. I have been able to put together a VBA code from google and other forums to match within all rows.
Code:
Sub matchdata()
On Error Resume Next
For Each cl In Columns(10).SpecialCells(2, 1)
Err.Clear
[Code] ........
1. I need to adapt the code so as to do matching within accounts. Example within ABC and XYZ separately.There will be 100s of accounts.
2. If possible, I need to offset a credit (negative value) with multiple debits. Example: ABC -5000 would offset ABC 2000 and ABC 1000. I am not worried about the remaining balance.
View 1 Replies
View Related
Feb 6, 2007
I use data on a text file and import this into Excel and use a macro to format. The data lists invoices and credit notes and their dollar value.
Due to a fault in the program that creates the text file all dollar amounts are shown as positive figures. Credit note amounts should correctly be shown as negative figures.
The invoice number format is 8 characters beginning with a letter and credit notes begin with a number.
Is there macro code that will change credit note values to a negative figure?
View 9 Replies
View Related
Nov 20, 2007
I have a series that comprises of two rows:
Row 1 is date
Row 2 are values
I am trying to find a formula where I can identify where in row two the value chages from positive to negative, and where it changes from positive to negative it should return the date the value changes from positive to negative ie:
ROW 1 08-Jan 09-Jan 10-Jan
ROW 2 1111 22222 -33333
The formula should identify that in row 2 the negative value occurs at -33333 and at the 10th of January.
View 5 Replies
View Related
May 17, 2008
VBA code to change a cells value in a column and the adjacent column to the right of it. If the column has a negative number in it, I need to put the absolute value of that number into the next cell over on the right. I then need to change the original column values to 0. I've added a spreadsheet that shows an example and the desired output.
View 3 Replies
View Related
Mar 28, 2014
I've got an excel sheet that lets people choose between 2 choices. I'm using scroll bars to make it easy for them to choose and visualize their preference with as little mouse clicks as possible. The scroll bar is only one sided though, meaning it starts from a min value to a max value.
I would like a scroll bar with a of 0, with the value increasing positively as the user scrolls to the left, and the value also increasing positively as it scrolls to the right.
View 2 Replies
View Related
Jan 24, 2013
I'm trying to count the number of times a payment is made within a certain set of dates. I believe that I should be using a countifs function, but I'm not exactly sure how to program it. I have a number of months, say the last 12. And in each month a payment is made or it isn't made.
View 6 Replies
View Related
