# Calculated field that references a cell in that row, and the entire field

#### jwb1012

##### Board Regular
Is it possible to reference a *cell* in a calculated field of access?

I want to add a column that takes the average of the "price" column, if the "product ID" column matches the "product ID" of that row, and the "date" column is less than the date in that row.

 No. Product ID Date Price *12-month Avg - Prior to this Purchase* Calculation 1 A 1/1/2016 10 = 0 2 A 5/30/2016 20 10 =AVG(1) 3 A 10/27/2016 30 15 =AVG(2, 3) 4 A 3/26/2017 40 25 =AVG(3, 4)

<tbody>
</tbody>

Last edited:

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Russell Hauf

##### MrExcel MVP

Edit: sorry, AVERAGEIFS

Last edited:

#### jwb1012

##### Board Regular
Is it possible to use AVERAGEIFS in Access?

I have a table of purchases with: Product ID, Date, and Price...for each purchase, I need to add a calculated column that shows the average price for the 12 months prior to that purchase date.

#### Micron

##### Well-known Member
That is an Excel function, and this is an Access question, no?
If the field you refer to is a table field, then you should not be storing calculated values in it. You can do calculations in a Totals query, averaging the price, Group By on Product ID, but I don't know what to suggest about your dates since it's not entirely clear to me what you want as results.

#### jwb1012

##### Board Regular
Hi Micron,

Yes, this is an Access question. I have moved away from trying to add a calculated field in the last column of my table and I am now creating a query. My table has a record of all purchases made (over a 5 year period). For each purchase, I need to determine what the "average price paid over the 12-month period prior to this purchase" was. So, if one record represents: the purchase of an apple, on 1/1/2017...I need to determine the "average price paid for an apple over the 12 months prior to 1/1/2017". This will allow me to compare the price I paid on 1/1/2017 to the average price I paid for an apple in the previous year. This average price could be made up of 100+ purchase scattered across different dates within that 12 month period.

#### jwb1012

##### Board Regular
But, I have many different products, so I cant explicitly refer to "apple" - I need to refer to the product in that row.

#### jwb1012

##### Board Regular
Okay, I have a new thought/approach. In my table "Table1", I have added a "start date" and "end date"... so in my query, I have the Product ID, start date, and end date. So all I want to do is create a query that pulls the average price for the product ID between those dates. This should be possible in access, right? I am veryexcel minded so I am having trouble wrapping my head around access.

#### Micron

##### Well-known Member
Very possible, especially given the changes you made. The answer is in post#4. Totals query as suggested, but using your start and end date fields as WHERE parameters, not GroupBy or Aggregate function parameters. Either start with a query that includes your table and switch it to a Totals query (summation character in the ribbon) and choose these parameters from the combo selector in the Totals: row, or try the query wizard.

Replies
1
Views
208
Replies
4
Views
369
Replies
1
Views
216
Replies
5
Views
201
Replies
0
Views
101

1,190,783
Messages
5,982,900
Members
439,805
Latest member
IDarkstarX

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back