I want to average cells in different columns which meet a minimum criteria, i.e. > 2.0, from specified categories list in the first column. Example: I want to select a category listed in column A and average the numbers listed in columns C and F for that category which meet a minimum threshold.
View 9 Replies
I would like to only view (or otherwise mark) the highest value cells (in column g) for each category (column c),
View 3 Replies View RelatedI have done alot of research in the formula realm but haven't ventured into the VBA coding side of the house due to the inexperience. I'm having issues w/ a particular calculation and hoping you all can help me out.
I hope I can explain this well.
- I have a total of 31 sheets (1-31)
- In each sheet there is a series of number (B16:L16)
- I want to take the average of all these numbers
I'm able to get the average of each sheet then take the average of those but that is not what I'm trying to do because of the following problem.
Let say in sheet1 that 5 cells are filled from the series (B16:L16) but on sheet2 there is only 1 cell filled and on sheet15 there are 10 of the 11 cells filled.
When you take the average of each sheet than average that it will not come out with the correct calculation. I'm trying to average ALL numbers combined from Sheet1 to 31 from cells B16:L16.
How can I do this. I have tried several solutions but nothing working properly. This is the one I have tried but doesn't work properly:
{=AVERAGE(If(ISNUMBER( '[Jul06AMEFVER.xls]1'!$B$15:$L$15),ABS('[Jul06AMEFVER.xls]1'!$B$15:$L$15)))}
However when I try to ":31" after the 1 it will not provide the correct value. Not sure where to go with this.
I wanted to average selected cells excluding the value zero. Also, I wanted to make the value of cell is zero and not to display the error #DIV/0! if the denominator is zero.
A1 = Denominator
Selected cell:
A3, A5, A6, A8
I have a row of dates going across in row 2, and random values going across in row 3 from D3 onwards. I want to return the average of values in row 3 until the third last non-blank cell in row 2 and do a similar thing for maximum row but just for the last 30 cells from the right (so latest 30 days). I have attached screenshot of part of spreadsheet for better illustration.
Capture.PNG
In my macro I can come up with a range for both, as below. Both lines of macro below select the correct range I am looking for.
[Code] .....
However when I try to apply them into an average/maximum function they returned an error 1004, Application defined or object define error:
[Code] .....
When I put it as following it returned me the value of Cells (3, 4) (Cell D3) instead of the average:
[Code] .....
Attached File : Capture.PNG‎
I was trying to sum/average last values in a range. If the range is updated, it should sum/average the last 5.
Tried to do it myself but succeded to do my head in only.
I am having difficulty trying to solution this:
Row 1 = Dates e.g. 1-Mar - 31-Mar
Row 2 = Day of Week e.g 1-Mar(B1) = Sat(B2) through 31-Mar ending at (AF)
Column A has hourly time intervals
A3 = 0:00
A4 = 1:00 etc to 23:00
Numbers fall into cells by date/day and interval up to today 10-Mar
What I am trying to do at is average the days separated by Weekdays and weekends..so the formula at AG for interval for weekday would be =AVERAGE(D3:H3,K3:O3,R3:V3,Y3:AC3,AF3) weekends (Column AH)would be =AVERAGE(B3:C3,I3:J3,P3:Q3,W3:X3,AD3:AE3)
I want to average the weekdays and weekend numbers without having to group the weekdays and weekends in a custom sort in the final column. That way when I add the data every day, it auto calculates in AG and AH. If I do it as it shows above, the AVG is skewed due to the blank cells.
Attached a sample worksheet.
I have data for 500 companies over 10 years, in three criterions: “EPS”, “DPS” & “PX” (i.e. earnings, dividends and price).
For each company I have four lines recording the data, and given a letter code indicating how the change in dividends and earnings have been according to the following
(Examples)
DD-ED = Dividends Decreased & Earnings Decreased
ND-EI = No Dividends & Earnings Increased
As can be seen below this code is present for each company
What I need help with is some sort of lookup function which takes the average of the price changes (which is given in numerical values) only for the companies which code is equal to the code in the reference field.
So something like: =AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353"",D2:D2353))) (currently counting every 5th line, since there is 4 lines between respectively PX/DPS/EPS for each company) –But with a constraint indicating only to include the value in the calculation if the codes are the same (e.g. "DI-EI" = "DI-EI").
So that the value, which is currently 20.96% (which now includes ALL price values), would only include those for the respective group (in this case, companies in the DE-EI group).
I need to calculate the average spend on a day of the week over the month, so all Monday's or all Tuesday's, etc. One sheet is one week so I need to average b16 on 6 sheets as an example.
I used =AVERAGE('WEEK1:WEEK6'!B18) to calculate average over the six Monday's. The issue is, as in other posts, how do I ignore the cells that have a zero or null value.
I've tried adjusting this which was in 1 post
=AVERAGE(IF($C$2:$CA$2=C62,IF($C$25:$CA$25"",$C$25:$CA$25
with this
=AVERAGE(IF(1+1=2,IF('WEEK1:WEEK6 '!B180,'WEEK1:WEEK6 '!B18))) which returns #REF!
this from another post
=SUM('WEEK1:WEEK7 '!B18)/COUNTIF('WEEK1:WEEK7 '!B18,"0") which returns #VALUE!
and this
=AVERAGE(IF('WEEK1:WEEK7 '!B180,'WEEK1:WEEK7 '!B18)) which returns #NAME?
The cells on each sheet are sum formulas for other cells on the sheet not just numbers on their own.
Using windows 7, excel 2003
Could somebody please suggest a formula that will sum values at each change in category?
I've played around with a pivot table, but for this it really doesn't fit.
Column A is the category and there are totals in column B for each entry.
The only place I need a formula is at the change in First and Second...so for any second all of the third's plus that second and for the first everything below.
Attached is an example with outcome in column C
xxxx First
xxxx Second
xxxx Third
Is it possible to make a ComboBox with categories. I have provided a sketch below of how I want my ComboBox to look like. I don't want the user to be able to click on "Fruits" or "Vegetables", but they should be able to select any of the other options.
ComboBox.png
I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 3 Replies View RelatedI am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 10 Replies View Related The ones we need to focus on are Columns A,C, H, and AG. Daily this sheet ‘Raw’ will be filtered and data sent to sheets 100,200,300.
In the Raw sheet we have many rows of data, but when this is transferred to the other sheets we will only see one row of data in each sheet.
1. The Date from Cell(C2) should be copied to Cell ‘A3 or next empty cell on column A’ on all 3 sheets(100,200,300)
2.In Column A there are 3 accounts – 101,201,301
If Column A value = 101 then send the corresponding Column H (P&L) value to Sheet”100”, If Column A =201 then send to sheet ‘200’, If 301 then to sheet ‘300’
Now that’s not quite what I need, the Column H values in some cases have to be added up – by category. You can see this on sheets 100 – 300. Columns B to K on Sheet(100) have the different categories of products. What I want is a code that would go through Column AG in Sheet(Raw) match the category name to the category name in the Range(B2:K2) and copy the P&L figure (Column H value in ‘Raw’) there.
e.g. I need all the rows pertaining to account 101 with the same label ‘Interest Rate’ to have the P&L (Column H) added up. So I would get the value 188760 in Sheet ‘100’ Cell D3. (I just added H2,H3,H12,H13) ...
How do I summarise the attached table. I want to have a drop down menu in the MONTH cell (B13) that summarises the month nominated. Also a drop down menu in CATEGORY that I choose (B14). The value of the category in any particular month is totalled in B15.
I know how to create drop down menus and how to define ranges - just not sure of the formulas to use in order to get the correct answer in cell B15....
I have a given set of data. Each row of data fits into one of 7 different categories. Besides doing a simple filter to be able to pull out a certain "category", how can I do this differently? Is there a code/macro process I need to go through? Basically what I need to do is find a way to group these categories together, so that someone searching for a certain category can easily pull it from the data.
View 8 Replies View RelatedI have a series of up to 40 or so entries for given dates. These entries are categorised by either "bid" or "offer" and I'm trying to find a way to chart them both using a pivot table. I'm stuck as it seems that a pivot can only count the number of bids and offers on any given day, but it cannot differentiate between them.
Note: that the original list the pivot depends on will constantly be updated so I can't just develop a custom chart.
I've attached a spreadsheet with an example of what I'm playing with. Pivot graph is in "PIVOT" worksheet, data is in "DATA".
pivotland.xlsx
I have a file and it has a table on it with 2 categories.
what i want to do is select a color in the first drop down list and than in the second only select the items that match the color i have previously selected.
Is there anyway to do this with named ranges because i would like to index everything in a table.
I have a small online business and am slowly learning Excel to keep my records. I looked at Quickbooks and I think that it just a little too complicated for my needs, besides I like excel better.
The spreadsheet I want to make is how can I summarize the different categories, shipping, travel, EVSE, Wire, or whatever I come up with in the future from a daily expense spreadsheet. I guess the summary should be on another page.
I also guess I can make up a total also of the companies I buy from...
I've attached a beginning daily expense spreadsheet with some entries.2014 costs.xlsx
1. I would like to be able to play with it by choosing different categories, sub-categories etc (adding different ones etc.). I tried pivot table but to get this data in pivot table format is a very time consuming task to start with.
2. The sample file has only one month. I get this type of data every month. So have to annualize using other months etc. I could get different months in a column also. So for example column d could have Jan, Feb, March etc. for row of data. But that would mean I will have 1922 * 10 = 19220 rows. And when I update this every month row reference from formulae (If I had formulae) will need to be updated.
I have the flexibility of getting this source data rearranged little bit but not too much flexibility there.
I have a column of numbers that look like this: 111222111222111 and I need them to look like this: 111 222 111 222 111. The numbers are all random and not 111222.
Then I have a second column with numbers like this: 11112222111122221111 but they need to be like this: 1111 2222 1111 2222 1111.
I am building a tracking list for several items and have categorized them
Software
Hardware
Networking
I will have several of these and the list keeps getting bigger. I have a table above the list that summarizes these catergories and the count of them. How can I count each categorie and so that it updates each time I add one.
The formula would have to sumproduct or determine what categories are there and how many (unique list) and update when I add one.
Currently its a manual process of creating a unique list and doing a countif.
I have a list of customer satisfaction scores that are pulled in from a sharepoint list. The list is then used to create a pivot chart that is used in a web part on a dashboard in sharepoint. The chart is supposed to show average customer satisfaction scores per category per month.
The problem I have is that I can't filter the categories using a slicer, I can only filter the categories' values (i.e. the scores). I can filter by date though.
When I try to filter the various score categories/series (Support, Change Management etc) I only get the option to filter their values i.e. 1,2,3,4,5.
I want to be able to use a slicer or similar to be able to display either all of the series or just selected ones.pivottable.JPGpivotchart.JPG
I have two columns, in column A will have four or five categories repeating randomly, in columns B will have unique names.
What I would like to do is to define categories as a column names in a table and then to automatically fill that table with the data from column B depending on category.
Drop list. First of all I have sheet with all my data and i have define the name of each table.
I have about 15 categories table and a table with min 2 and max 100 items in each category.
I have made in a different sheet a drop list for my categories. Near this list i made another drop list with the items by using the formula =indirect() to arrange my items by categories.
The problem that i have is:
- Some categories they have many items and is difficult to find some items. i need to put a formula to put some letters and find what i need easily (easy searchable).
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total
1234Bed150
5678Bed100
1547mattress52
58658mattress188
12356bed12
8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?
View 6 Replies View RelatedI have a column which lists total spend by vendor. I am using the sumif formulae to sum spend in certain categories, for example spend greater than £500,000. I would like to use two criteria, for example greater than £300,00 but less than £499,999. I am also using countif to get the number of vendors in each range of spend. How do I adapt my current code of "=SUMIF(D2:D1463,">300000",D2:D1463)" to look at greater than £300,000 but less than £499,999. I'm sure this is simple but like most things only simple if you know how.
View 2 Replies View RelatedI have a column that has subcategories, and I would like to create a new column that would group those subcategories into categories.
Is there a formula that once it detects a certain word in the subcategory should give a certain category name that I choose?
Using Excel 2013. I have a pivot chart with date as the x-axis, 1 y-axis series on a Primary and 1 series on a secondary axis. My pivot table is sorted in descending order by date because I need the most recent date on top. To chart the date in order from left to right, I've turned on Categories in Reverse Order .
1. When hovering over the data points on the primary series, the date is reversed. Example: the data point Jan31 says Jan1.
2. The bigger problem is the series using the Secondary Axis is plotting Jan31-Jan1 instead of the Reverse Order .
Is this a problem with Excel or is there other options that I can try to correct all this?
