I have 2 tables:
IDTypeValue
1A3
2A3
3A4
4B4
5B3
6C2
7A4
8A3
And
TypeTotal
A
B
C
I need to "total" column to automatically display the sum of the values for the categories A, B and C. Is there a formula I can use for this?
I am using Excel 2011 for Mac.
I am creating a workbook with multiple worksheets, one overview sheet and then one sheet for each month of the year.
In the monthly sheets I have a Category column for which I have created a drop-down menu of expense categories. Then I have an Expense Amount column where I enter the amount spent.
The Category column will not necessarily remain sorted by category because I will be entering the expenses as they come up throughout the month so the categories will be all mixed up, unless I manually sort them.
What I want to do is somehow automatically calculate a running subtotal by Category (that will update with each new entry) and simultaneously automatically transfer the running subtotal for each Category to a specific cell on the overview sheet.
Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
Category
Genre
Title
Author
Year
[Code]....
I have been using Excel for a while but i'm kinda stuck at one point where i have to calculate the sum of the numbers corresponding to the month of the data.
Column 1 has all the dates in different months - in this workbook i've included only jan, feb and mar. The second column has the counts corresponding to the dates.
Now if a user inputs the month (1,2,3) number in F2, then we need to compute the sum of all the numbers from second column corresponding to the month in column 1 and display in cell F4.
Tried to explore with sumifs but failed terribly.. does anyone know a formula that can sum up figures in different columns under the same Category.
Have attached a sample xls..
I am creating a spreadsheet that allocates hours to different categories of work - for example, "Fire Protection". I need the allocated hours for all of the different Fire Protection duties to only show up when a value greater than zero is entered in the column at the top of this category entitled "Fire Protection". If "Fire Protection" has a value of zero, then all fire protection-related duties will automatically be zero as well - in effect, this category of hours is nullified.
View 1 Replies View RelatedWorksheet 1
I am calculating group averages for the following performers - very good, good, average, low, very low - for a series of factors.
Worksheet 2
Contains the same factors with the values for which Im trying to work out the average. Each factor has a performance rating above it, either very good, good, average, low, very low.
I need a formula which will match the performance rating from worksheet 1 (I3, J3, K3, L3, M3) to worksheet 2 and then calculate the averages of each factors based on those matches.
The goal here is for users of this form to enter the "type" of tool and automatically create a list of tools based on the "type" in another sheet (NEW SHEET). See the heading on that sheet in the file for more info.
Also, keep in mind that this would be a template (.xlt) for 2003 and all the sheets would be individually locked -- password protected.
Since this form will be filled in by folks that aren't very computer savvy in general, I'm trying to make this as simple/automatic as possible. I myself have a limited toolbox as well. I'm not entirely sure a program would be needed (IF statements in the cells?)
I have imported a list of information from a text file created by my network admin. I need to take the information from different categories in the list and sort them by category. All of the information is in once cell, but each has a common prefix (ie. URL = or Username), etc. After each prefix is the information I'd like to sort under the category (each category is the prefix). All of the information (prefix & data) is in cell A because it was a text file, and I'd like to simply find a way to say if A10 = URL= then place the information in C10...Below is what I need to sort.
URL = [url]
User Name =
Source IP = 0.0.0.0
Destination IP = 12.345.67.89
Disposition = CATEGORY_BLOCKED
Lookup Code = WISP_URL_BLOCKED
Category = Proxy Avoidance
Elapsed Time = 0 ms
AVG TIME PER REQUEST = 0 ms
------------------------------------------
Sending URL_LOOKUP_REQUEST_EX...
------------------------------------------
URL = [url]
User Name =
Source IP = 0.0.0.0
Destination IP = 123.456.789.101
Disposition = QUOTA_USER_BLOCKED
Lookup Code = WISP_URL_BLOCKED
Category = Social Networking and Personal Sites
Elapsed Time = 0 ms
AVG TIME PER REQUEST = 0 ms
I am trying to format the colour of a column chart based on the value of the x axis (not the y axis value). Firstly I need to know if it is possible. Secondly I would appreciate any hints / VBA code that could be useful.
View 4 Replies View Related I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.
Basically I have a sheet (called Sheet1) with the following columns:
A - Date (by individual day DD/MM/YYYY)
B - A catagory (so using the old example, Apples, Orange, Pears)
C - Price.
I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).
I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
View 2 Replies View RelatedBelow the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
I need new workbooks based on category name in the below list. Respective sheets should be copied from the existing workbook with items as sheet names. List can be updatable.
category(workbookname)Items(sheets)
Vegetables carrot
vegetables brinjal
meat chicken
meat Fish
meat Mutton
grains Wheat
grains Rice
I have an excel sheet in which I have 3 columns, Column A is giving me a week number from date entered in Column B. Column A is a merged Cell. There are values in Column C.
I want to be able to sum cells of column C ( not entire Column, select few cells) who show 1 (representing Week number) against them in Column A. I have been trying to sort this out since Thursday and have had no success.
Please see attached example : book1 (2).xlsx
In in Excel/VBA, I have been making OWC charts on a form. My aim is to chart categories (x axis) against values (y axis), showing several values in each category as markers. In addition, I have been calculating the mean for all values in each category, and plotting this as a line across the categories. I first set up the X axis at the charts level:
Set oChart = chtspcXIC.Charts.Add
With oChart
.HasLegend = True
.HasTitle = True
.Title.Caption = "Used fractions per timepoint"
.Type = chChartTypeLineMarkers
.SetData chDimCategories, chDataLiteral, XICtmpts
End With
Next, for the individual points, I made two corresponding arrays, one of categories and the other of values and attempted to send these to a new a seriescollection:
Set oSeries = oChart.SeriesCollection.Add
With oSeries..............
REFER TO MY ATTACHMENT.
Can I get a simple ONE LINE formula to calculate the below combination
Basic Cost = 100 (A) (my Material cost)
ADD : 10% for Freight (B)
A+B = C
ADD : 10 % for Overheads on 110 (On C) = D
C+D = E
ADD : 15% towards Margin on 121 (on E) = F
E+F = G
WHERE G = 139.15
I require this because my %s varies according to products.
I am using a vlookup and have a problem. I am assigning a category to an item number based on the first two characters of the item number. For example item number 60123 would equal scrap because of the first two characters of 60. But the item number can begin with either a number or letter. Here is the formula I am using that works for item numbers that begin with numbers:
=VLOOKUP(VALUE(LEFT(E2,2)),Sheet3!A:B,2,FALSE)
It works fine until I reach a item number that begins with a letter, then I get the dreaded #Value error. If I take the value out of the formula then it works for the letter based number items but not for the number based item numbers.
I have a list where i enter invoice values lets say on column (A:A)
and i want to calculate all the invoice values each time i enter the values on A:A
for example;
i entered $2500 on cell A1 and pressed enter,
then i entered another figure on A1 let say $500 i want the total value to be shown such as $3000
What is the best way to check to be sure that a text box contains numeric values prior to performing calculations?
I have a form that has many textboxes which a user enters values into to perform calculations. I want to make sure that each necessary field contains a numeric value prior to performing the calcuations.
This is what I have so far but for some reason it keeps giving me the message box:
If WorksheetFunction.IsNumber(Me.specGrav_txt) = True And _
WorksheetFunction.IsNumber(Me.pAngle_box) = True And _
WorksheetFunction.IsNumber(Me.pLength_box) = True And _
WorksheetFunction.IsNumber(Me.pSize_box) = True And _
WorksheetFunction.IsNumber(Me.pQty_box) = True And _
pModTrap_btn.Value = True Then
PrimMTcalc
Else
If WorksheetFunction.IsNumber(Me.specGrav_txt) = True And _
WorksheetFunction.IsNumber(Me.pAngle_box) = True And _
WorksheetFunction.IsNumber(Me.pLength_box) = True And _
WorksheetFunction.IsNumber(Me.pSize_box) = True And _
WorksheetFunction.IsNumber(Me.pQty_box) = True And _
pFullRnd_btn.Value = True Then
PrimFRcalc
Else
MsgBox "There is missing data." & vbNewLine & _
"Please check to make sure that all runer data has been entered" & vbNewLine & _
"and the Specific Gravity field contains a numeric value."
End If
End If
In the chart I attached there are 20 columns and 7 rows. The calculations are as follows: K in first period - 8 (given). product per worker (y) - 2*K^(1/3) = 4. Total invesment - s*y - s (savings rate) is given as 0.1 and y was calculated in the previous line. s*y = 0.1*4 = 0.4
Depreciation on capital - deperciation rate is set at 2.5% (0.0025) so the depreciation is 0.0025*k = 0.0025*8 = 0.2. Net investment = Total investment (0.4) -Depreciation (0.2) = 0.2. Consumption per worker = product per worker (4) - Net investment (0.4) = 3.6
Growtn in product per worker - Can only start calculating from next period. It is (yt-yt-1)/yt-1. (Next year's product per worker minus this year's product per worker, the answer is divided by last year's product per worker).
log linear Regression. The thing is I do not know how to calculate elasticity of logarithmic values, slope, std. error in order to make forecasts. Is there a way solve it with Linest or any other formula. I am attaching a spreadsheet to better see.
View 7 Replies View RelatedThere are five levels of Performance.
Not Evident(o), Emerging(1),Operational(2),Highly Functional(4),Exemplary(5)
There are ten rows of Categories of Performance.
There is one final column of Performance Summary for each category.
There are seven columns that are report sources of Performance.
There is one final row of Performance Summary for each report source.
There is one cell that is a final Summary of Performance Overall.
The cells have drop down lists of words only.
I need the Summary cells to show the result of calculation of the average for each row and column.
I am making a summery table of lots of soil contaminant data.
In my summery table, for each determinand I want the average value automatically taken from data from a number of other tables within this same spreadsheet.
For example, for pH- I would average the pH value found in each table, from cell D6, G24 and AB9.
The problem:
Out of the three tables where I take each pH value from, on occasions only one will have a value- the other two will be "0".
How do I ensure that, when averages are taken, it will always ignore '0' values?
I have a list of group ID codes, which contain 1 or more product codes within them.
Some product codes contain an "alternative" value (the actual value is irrelevant) and others do not. See example below
Group ID
Product Code
Alternative
56381
240027
160380
[Code] .....
Ultimately what I need to achieve is a percentage of how many product codes, within each group ID, contain an alternative.
So for the example above, group ID 56381 would have 50% codes with alternatives.
By counting the occurrences of a group ID and whether 1 particular code has an alternative I have calculated individual percentages against the size of the group e.g. product code 240027 (given a value of 1) divided by the total number of codes in the group (4), returns 0.25. Obviously doing this across the whole group would give my result (0.5 or 50%)
However some groups contain over 100 codes and the spreadsheet is 40K rows!
I have several listboxes in userform. They are named like this:
Work1, Salary1, Workforce1, Hours1, Price1
Work2, Salary2, Workforce2, Hours2, Price2
Work3, Salary3, Workforce3, Hours3, Price3
...
Work24, Salary24, Workforce24, Hours24, Price42
Workx listbox contains text, others contain numbers. Now I need Pricex listboxes to contain the value of this calculation: Salaryx * Workforcex * Hoursx (For example Price17 listbox value would be Salary17 * Workforce17 * Hours17)
However, if Workforcex listbox is empty, then Pricex listboxes should contain the value of this calculation: Salaryx * Hoursx Then again, if user wants to skip entering Salaryx, Workforcex and Hoursx, I want to allow entering value straight to Pricex listbox. Then Salaryx, Workforcex and Hoursx listboxes should be enabled.
On the other hand if there already are values in Salaryx, Workforcex or Hoursx listboxes, then the Pricex listbox should be not enabled. I need the values of these calculations to be inserted in Pricex listbox right after there are enough inputs without pressing any button etc. (For example if there are values in Workx, Salaryx and Hoursx inboxes, the calculation should be entered to Pricex listbox immediatelly.
I need to calculate a subtotal like value on the last row of a filtered region.
e.g.
description year sum
name1 3 yrs ago 200
name1 2 yrs ago 230
name1 last year 250
name1 forecast 220
name1 forecast value 1400
name2 3 yrs ago 200
name2 2 yrs ago 230
name2 last year 250
name2 forecast 220
name2 forecast value 1400
How to put a value on the last row of filtered column. let's say that above list is very little in comparison with the real list. so, let's say that i look up for name36, witch begins on row (36*5) -> 5 is the values of the year column; after autofilter with "name1" the very left column with row index become blue and show rows from 1 to 5, than, next row is the last non-empty row.
Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.
My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?
Const intCells=9 'actually, this is no constant but a variable
Const i=4 'actually, this is no constant, but a nested for- loop-value
Const intLinesInBetween = 12 ' amount of lines, which separate my values
Dim j As Integer 'simple counter, runs through all cells
Dim rngUpperRow As Range 'Range of the upper row = normalized time vector
Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted
Dim intCells As Integer ' amount of experiments in range............
I am in need of a formula to calculate the even distribution of values between two set values over a set number of columns. For example take a look at the attached screenshot. I have the numbers 2.88 and 2.44 and I need to fill in the empty cells between the two with with an even distribution of numbers in between the two (in descending order) over the 8 columns Screen-shot-2014-02-16-at-3.33.08-PM.jpg
View 1 Replies View RelatedAn age-old controversy has resurfaced in our lab regarding the calculation of a mean and standard deviation of a set of two numbers. There have always been those who say that you can't have a standard deviation of two numbers, but now someone has said that a mean of two numbers is meaningless (no pun intended). What is the current thinking on this? Of course, mathematically speaking, you can certainly calculate these values with only two numbers, but is a mean of two numbers not considered a valid measurement? How about a standard deviation?
View 2 Replies View Related