Basic Excel-2010 functions.
I have given a table with some decimal values like e.g 0.3658 0.358 0.485 0.7863 0.558 0.858 0.985, I want to find the sum and average of these number.
1) I have tried the autosum or auto Function =AVERAGE(A2:A7) but it yields #DIV/0! error
2) when I use =AVERAGEA(A2:A7), it yield ans=0
3) puttin =ROUND(AVERAGEA(H2:H23);3) also yields 0;
Please find the attached MS Excel 2010 file <average set.xlsx>.
There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.
Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.
The Column H and Column I also same as like above , but here negative set of numbers starts first.
[URL]
I tried the following formula on excel 2010:
=ROUND(2.01,2)-ROUND(2,2)-ROUND(0.01,2) but still I am getting -2.13371E-16 as a result.
How can I make it return 0 instead? I am trying to compare the above formula with 0: =2.01-2-.01<0. it returns TRUE, which should be FALSE.
Using Excel 2010
Workbook with 21 sheets, first sheet is the summary sheet (named SUMMARY) taking all it's content from the other sheets, named from 1 to 20, which are all laid out the same, but contain different data.
My problem is that I want to get rid of decimal places that are being brought into the SUMMARY sheet
In sheets named 1 to 20, I am using the following formula in H23 to arrive at a figure =IFERROR(100/(B5/B23),0) in H23
B5 and B23 are always whole numbers on all sheets 1-20, result can be whole or contain decimal places which I round up in the cell to show no decimal places
On sheet 1, B5 is 200 and B23 is 5, giving 2.50 as the answer, which is right, I have H23 set as a number cell with no decimal places so it rounds up to 3 which is what appears in the H23 cell.
All of the above is correct and appearing as I want it on the sheet, the problem arises when I import into the SUMMARY sheet as follows:
On the SUMMARY sheet in cell B59 I want the following to appear 5 - 3 but am getting 5 - 2.5 using the following formula
='1'!B23&" - "&'1'!H23
The first part is fine as B23 will always be a whole number
How can I stop it from bringing in the 2.5 and get it to bring in the rounded up 3 into cell B59
I have cell B59 set as a number cell with no decimal places but it still appears as 2.5
Also when I correct it as it appears a lot on the summary sheet, how do I get the following to happen
='1'!$B$23&" - "&'1'!$H$23
if I fill the above across columns how to I get the two '1' s in the formula to become '2' then '3' as I fill across.
I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.
e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..
I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same
How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?
I am using Excel 2010 and I am currently trying to find the Avg Min / Avg Max costs for 4 different groups.. Players, Accompanying, Coaches, Medical.
I need it to get the minimum values from 3 different sections ( I am using custom names for them such as PlayerReg for Player Registration costs ) once it gathers the minimum / maximum values from the 3 different sections it needs to add them together to display the Avg Minimum / Maximum for each group.
At the moment I have =MIN(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Minimum /w Frequent Flier although the minimum value it should detect is $948.75 it displays $1115.
For maximum I have
=MAX(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Maximum /w Frequent Flier although once again this value should be $1174.50
Although for some reason it displays $978.75 - which is the minimum cost. The Maximum figures for the other groups (Accompanying, Coaches and Medical) don't even display values Instead it just has #VALUE! in which I'm guessing I have something wrong with it.
Data example is displayed below. I need to know how to find the average, not date average, but count. What is the average per day? Example on 2/9/2014 there are 12 entries but on 2/10/2014 there is only 1. Example: For all the lines of data that I have, what is average per day?
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
[code].....
I have a sequence of 40 columns of data, representing 10 weeks worth of events with 4 values per week
I need to average the first pair for each week, ignoring zeros and blanks, and also separately the 2nd pair of cells, across the whole 40 columns
I am trying to come up with a neater solution which takes into account the zeros, at the moment I have the following which does not, for row 34
=IF($AR34>0,ROUND(AVERAGE(D34:E34,H34:I34,L34:M34,P34:Q34,T34:U34,
X34:Y34,AB34:AC34,AF34:AG34,AJ34:AK34,AN34:AO34)*2,2),0)
I could use SUM, COUNT COUNTIF etc but its gonna get messy
Using EXCEL 2010
I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).
Sub average()
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
For I = 3 To FinalRow
If Cells(I,3).Value 0 Then
'I use the above to identify the first nonzero cell
ActiveCell.FormulaR1C1 = "average(........)
This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.
In our sql server table, we have a field of type DATETIME. This field is populated by a vendor product with the elapsed time of an event.
We created a SQL Server view of this column and other columns of info joined together from several tables.
Then we create in Excel 2010 a new spreadsheet with a data connection to the SQL Server, instance, and view that we created.
Excel 2010 displays each of the columns in our view.
The elapsed time column appears as "dd hh:mm:ss". However, when we click on a cell to look at its formatting, Excel lists it as general.
We want to calculate the average time in this column.
We go down below the data, click on a cell, and insert =Average(C2:C17215) and we get an error saying dividing by zero.
We have 17,000+ rows of data, so there is no divide by zero math going on here. Each row in this column has data, so there are no blank cells.
We have spent some time web searching. We then tried various methods of calculating this. Nothing has worked. In most cases, we get the divide by zero. We tried some solutions that mention using an array forumula, but that just changed the error to an invalid value error.
How I could create a chart (standard column) with an average line in Excel 2010?
View 4 Replies View RelatedUsing Excel 2010.
I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried
=AVERAGEIFS(DAYS360(A:A,B:B),A:A,">5/1/2012",A:A,"5/1/2012",A:A,"
Please find the attached 2010 version excel file <For a given date Time Range average .xlsx>.
I want to calculate the average value (Column C) for the given date (Column A) and given time range (Column B).
Values to be plotted in the color cells yellow and light pink / magenta.
Voting Results.xlsx
1.jpg
I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
[URL]
Excel 2010 Higher than past average formula?
Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...
View 2 Replies View RelatedI am using excell 2007 and 2010
I need to find the average numbers on sheet 1 column D. Column D contains numbers as well as Blank cells. The info will be presented on sheet 2 in a monthly view. In other words i need to ssearch awithin a date range and determin the average of those numbers. This works on some months but return a DIV/0 erro most of the time.
=AVERAGEIFS('2N Stats'!$I:$I,'2N Stats'!$E:$E,">=2/1/2011",'2N Stats'!$E:$E,"
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
I have this text at J22 and i would like to break out 13 and 95 000 out of this:
<td><span>13</span></td><td class=""right_align""><span>0-0-2</span></td><td class=""right_align""><span>95 000
The numbers and lenght of the numbers where 13 and 95 000 are may vary in lenght. I need them to parse the values at those spots to separate cells.
Im using excel 2010
Open a brand new spreadsheet whether the format is set to "General" or "Text" it will not let me enter numbers. For example, when I enter 2007 in the cell it displays 20 07 not really that big of space between the two zeros - but still a space. I can't format this no matter what I do.
View 1 Replies View RelatedIn Excel 2010, I have a list of numbers with about 7000 rows. I am trying to track the number of times the number "22" appears within 30-day periods, with the end-result being a "count column".
While I could enter a formula manually for the date ranges, there are over 200 time-spans I'd have to enter (list goes back to year 1994). Is there a way to automate this process...
Excel 2010ABCDE1DateNumbers List# of Days AgoCount for number
22205/31/12304305/30/12602405/29/12909505/28/12301202605/27/12391505705/26/12371807805/25/1222210
11905/24/123824051005/23/122227091105/22/123230011205/21/123533001305/20/12383608
[Code] .........
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
I have a column of Dates in Col B with corresponding X values in Col C. I specify a start date in Cell F14 and an end date in Cell F16. I then use the function shown in cell H6 to identify the starting row number (corresponding to start date) in Cell F6. If I used the same function to identify the end row (in this case 4/2/13), it would choose row 8 which is the first row in which it encounters that date. Unfortunately, I need the end row to identify the last row that date appears in. In this case, that would be row 11.
Excel 2010ABCDEFGHIJKLM1 Date and TimeX2
3/30/201334/1/2013 1:366.9744/1/2013 4:563.95 54/1/2013 4:573.27
Start Row64/1/2013 5:165.553=MATCH(DATE(YEAR(F14),MONTH(F14),DAY(F14)),$B:$B)+174/1/2013 5:172.35
End Row84/2/2013 17:191.59894/2/2013 17:192.81 104/2/2013 17:252.14 114/2/2013 17:262.05 124/3/2013 5:152.84 134/3/2013 5:154.11
Start Row144/3/2013 5:173.2404/01/13154/3/2013 5:173.45
End Row164/3/2013 17:124.0304/02/13174/3/2013 17:133.621819Sheet1
I would like to combine 2 columns, containing numbers on it, is it possible?
I have found the other option how to do it
=A2&B2 but then this doesn't work
I have
COL A COL B
12345 0000
The result I want would be= 000012345
Using Office 2010.
I am trying to find duplicate numbers in sets but so far I can only highlight the ones that are in exact order. I need to find each set that has the same numbers, in any order. Example..
I will provide an example of sets of 3. But I get 3, 4 usually but sometimes 5 or 6.
I get them from different people.
Person A- 234, 569, 498, 849, 848,343,567,347 etc...
Person B- 432, 596, 677, 566, 565,433, 455 etc..
Now I need to find each set that has the same numbers, any order. Like 234 from A and 432 from B would be the same, so I would need to highlight them 2 sets. But I can not figure out how to do this. For Excel to highlight it they have to be 234 and 234. Does not recognize same numbers, different order.
I am working in Excel 2010 (Windows 8) and am having real problems trying to find a function/formula that will work with both numbers and text. I am trying to identify if the contents of Col A cells and Col C cells on each row are duplicated within a worksheet, and if they are, to add a 'J'.
(Column A is a list of numerical company codes and Column C is a list of adviser codes using the letter A-G. If a company ID code has more than one of the same adviser codes referenced to it, it is termed 'Joint', hence the 'J').
The formula I've used is: =IF(A2:C2, FREQUENCY>1, "J")
But it tells me 'numbers only'.
I am using Excel 2010 and I am trying to calculate 4 different factors' (columns) values which are numbers. Based on my analysis I will assign the value from 1-5 for each factor and at the end I have to decide the priority (a new column) adding all 4 columns' numbers. My Priority column has three criteria (High, Medium, and Low) but I don't know what formula/function to use and how. I have tried IF function but did not work.
View 9 Replies View RelatedI need to remove numbers from a string of text and put them into new cells in Excel Starter 2010. There are two different values which need moving. The first is in parentheses and the second follows the parentheses.
Here's a few examples of what I mean:
Original text
RED008 - Wickaman and Hoodlum bandwidth (0.06Gb @ 2.00) 0.12
MFR005EP - Various Artists bandwidth (0.19Gb @ 2.00) 0.38
RAZORS010 Future Cut bandwidth (0.01Gb @ 2.00) 0.01
would like to split into...
Cell 1
RED008 - Wickaman and Hoodlum bandwidth
MFR005EP - Various Artists bandwidth
RAZORS010 Future Cut bandwidth
Cell 2
(0.06Gb @ 2.00)
(0.19Gb @ 2.00)
(0.01Gb @ 2.00)
Cell 3
0.12
0.38
0.01
I'm using Excel 2010.
A formula I am using is outputting "0" instead of the expected value. Upon evaluating the formula I realized that this was because some of the values - years, in this case - had quotes around them while others did not. Please see the image below for the screenshot of the evaluation.
These values - the years - are being evaluated in the following formula.
Code:
=SUM(
IF(inventory!$F$3:$R$3=$A3,
IF(inventory!$B$4:$B$56=$G$1,
IF(inventory!$D$4:$D$56=$G$2,
inventory!$F$4:$R$56))))
This formula references the following sheet (an excerpt from that sheet). You can also see the formulas found in the cells causing the problem.
*ABC5212/20/20102010Adjustments53*20112011543/17/20112011Carwen Printers559/9/20112011Adjustments569/9/20112011Copeland Printing
Spreadsheet FormulasCellFormulaB52=IF(A52="",C52,YEAR(A52))B53=IF(A53="",C53,YEAR(A53))
B54=IF(A54="",C54,YEAR(A54))B55=IF(A55="",C55,YEAR(A55))B56=IF(A56="",C56,YEAR(A56))
I suspect that the problem is being caused by the output of the formula in these cells. If I simply type in "2011" instead of using the formula in B52:B56, then the first formula in the code section above does not have a problem.
How I can reconcile this?