## I need to find the Average from Column A - but Reference Column B

MS EXCEL

### Next

• 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 >

### I need to find the Average from Column A - but Reference Column B

```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.
```

### Re: I need to find the Average from Column A - but Reference Column B

```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

```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
```

```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...
```

```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
```

```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".

```

```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.

```

### Who is online

Users browsing this forum: No registered users and 55 guest