Column A has a Range of Dollar Values ($0 - $100,000). Column B has a Range of numbers from 0 to 100. I want to return an average of the Dollar Values in column A but only using the Dollar Values that have a corresponding number in column B between the range of 0 to 10.
I need to find the Average from Column A - but Reference Column B
3 posts
• Page 1 of 1
- Exchange 2000
- policy
- mac
- Windows XP
- Microsoft OFFICE
- Microsoft publisher
- ADO
- ada
- printer
- FLASH
Jump to:
- 1. Copy cell to another as a comment
I import external data and I would like to take text in a cell and add it to another cell as a comment. I have a long list of part numbers and some have sizes. I would like to take the sizes and add them as comments to the part numbers. Not all part numbers have sizes so I only want the ones with data in the cell. Any Ideas? - 2. To long formula for excel
Like to find an answer too a long calculation in one cell, but the formule become to long just before I'm finish. A loop would have done it but I have no skills for to make it. what can I do to get around it? have tryied in many ways but excel do not let me get an answer to this. What can I do? Formula goes like this: =MMULT(MINVERSE(MMULT(TRANSPOSE OFFSET($L$80;DAG;0;Nobs;aksjer):OFFSET($L$80;DAG;0;Nobs;aksjer)-TRANSPOSE(MMULT(TRANSPOSE OFFSET($L$80;DAG;K$78;Nobs;aksjer):OFFSET($L$80;DAG;K$78;Nobs;aksjer));R80:R109)/Nobs)) OFFSET($L$80;DAG;0;Nobs;aksjer):OFFSET($L$80;DAG;0;Nobs;aksjer)-TRANSPOSE(MMULT(TRANSPOSE OFFSET($L$80;DAG;K$78;Nobs;aksjer):OFFSET($L$80;DAG;K$78;Nobs;aksjer));R80:R109)/Nobs))/(Nobs-1)); MMULT(TRANSPOSE OFFSET($L$80;DAG;K$78;Nobs;aksjer):OFFSET($L$80;DAG;K$78;Nobs;aksjer));R80:R109)/Nobs-U101)/ SUM(MMULT(MINVERSE(MMULT(TRANSPOSE OFFSET($L$80;DAG;0;Nobs;aksjer):OFFSET($L$80;DAG;0;Nobs;aksjer)-TRANSPOSE(MMULT(TRANSPOSE OFFSET($L$80;DAG;K$78;Nobs;aksjer):OFFSET($L$80;DAG;K$78;Nobs;aksjer));R80:R109)/Nobs)) OFFSET($L$80;DAG;0;Nobs;aksjer):OFFSET($L$80;DAG;0;Nobs;aksjer)-TRANSPOSE(MMULT(TRANSPOSE OFFSET($L$80;DAG;K$78;Nobs;aksjer):OFFSET($L$80;DAG;K$78;Nobs;aksjer));R80:R109)/Nobs))/(Nobs-1)); MMULT(TRANSPOSE OFFSET($L$80;DAG;K$78;Nobs;aksjer):OFFSET($L$80;DAG;K$78;Nobs;aksjer));R80:R109)/Nobs-U101)) - 3. EXCEL GURU WANTED - Time calculations
Problem is when taking a time-start cell from a time-end and applying a rate to the hours I get a different value from doing a simple calculation. ie simple calculation is 1.17 hours @ 130/hour = 152.10 Using times as Start Cell E2 = 13:00 & Cell D2 End 14:10 Calculating as: ((E2-INT(E2))*24)-((D2-INT(D2))*24) results as 1.17 Difference is 0.43? I have tried other calculations e.g. hour(d2) + minute(d2)/60) etc I am quite baffled can anyone help! If this 1.17 is used in the calculation the result is 151.67 - 4. Frequency and If with multiple Columns
Jym -- It sounds like you are trying to create a histogram, or at least the data to support one. Excel has a histogram method. If you've already installed it, it will be under >Tools>Data Analysis. If you haven't installed the data analysis tools it won't be there, but it's easy to install. Just go to >Tools>Addins, and click on 'Data Analysis' tools. (Don't click the 'Data Analysis - VBA', that's used for programming...). Then the histogram is available. You'll be able to make the table and/or chart, depending on what options you select. HTH "Jym" wrote: > > Hi and thank you for your time. > > I have the following data in a spread sheet > > A B C > 0 50 F > 50 52 D- > 52 56 D > 56 59 D+ //this continues through A+ it's our schools marking > system. > > On another sheet in the same workbook I have my 300 students listed > with their grade inputted as well. > > LastName FirstName Grade > Smith John 65 > > --------------------- > What I would like to do is find out the frequency of grades on another > sheet that might look like > > Grade Frequency > A 12 // where 12 means that 12 students recieved a mark > between 85 and 89 > > TIA > > > > > -- > Jym >
Next
I need to find the Average from Column A - but Reference Column B
by QkFNNzE4 » Wed, 16 Mar 2005 23:23:05 GMT
Re: I need to find the Average from Column A - but Reference Column B
by JulieD » Wed, 16 Mar 2005 23:40:55 GMT
Hi one method =SUMIF(B2:B27,"<="&10,A2:A27)/COUNTIF(B2:B27,"<="&10) as long as you don't have any negative numbers in B Cheers JulieD
Re: I need to find the Average from Column A - but Reference Column B
by Jason Morin » Wed, 16 Mar 2005 23:42:15 GMT
You could use the array formula:
=AVERAGE(IF((B1:B10>=0)*(B1:B10<=10),A1:A10))
In order to work, you must press ctrl + shift + enter
after copying in the formula. An alternative would be:
=SUM(A:A,-SUMIF(B:B,{"<0",">10"},A:A))/SUM(COUNT(B:B),-
COUNTIF(B:B,{"<0",">10"}))
The advantage of the 2nd formula is that it is *not* an
array formula and you can reference entire columns.
HTH
Jason
Atlanta, GA
Column B has a Range
column A but only using
column B between the
Similar Threads:
1.need to jump cells in column data when finding average, max and mi
I have a continuous column of data and there is data in every cell, but I want to find the average, max or min for every 96th block of data. For example, I have readings every 15 minutes for an entire day, but I want to find the max, min and avg. for each day without having to retype the max, min formula every time. I want to put the data in another column so I can make graphs...
2.need to find value from one column in another column of the sa
3.need to find value from one column in another column of the same r
I need a formula to look at a value in one column and look for it in another
column, in the same row. The second cell will have other various data in
it.the formula would appear in a 3rd column here's an example.
A B
C
8752 Irr Ltr fxd 3609 4/18/06 CAdrian, 8752 3/24/06
2611 Irr Ltr Fx 6091, 10418,7362,2734,9187 3/20/06
8316 Irr ltr fxd 8283 02/24/05 VOrtega,5466
4.Need Macro to Find Column Heading -- if none, then insert new column
Using Excel 2002 to manipulate an XML file and save as XLS file which will be linked as a table to Microsoft Access. The issue is that the XML file only shows table headings (i.e., column headings in Excel) if there is data. If no data, then no heading. Need a macro that will do the following: 1. Find column heading (e.g. "Scheduled Review Date") if exists, then end the if statement. 2. If heading does not exist, then insert new column and label column as "Scheduled Review Date". Thanks for your help.
5.Need to match 2 columns, if a match found add info from 2nd column
I'm comparing 2 lists of serial numbers. The first list is smaller. The 2nd
list is more extensive and contains information in 3 more columns that needs
to be added to the 1st list.
Example:
12345678 X 12345677
12345678 Mr Joe Bloggs Feb 08 拢1500
12345679 X 12345679 Mr Fred Smith Apr 08 拢600
12345681 X 12345680
12345682 X 12345681 Mrs Jane Doe Apr 08 拢750
If the number exists in both columns, I need the remaining info (name, date,
amount etc) to be inserted at X.
Please can someone help?
6. Excel - filter results in column, need to find adjacent column data
7. Referencing date column A & time column B to get info from column
8. Find something in column a then find if column B matches criteria
3 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 55 guest