ms excel query to calculate

**adri_i** · 05-27-2013, 08:22 AM

I am new in excel query. I have a database and I extract data from it, but I want also to calculate the ratio number/previous numbr. Can anyone help me?

**alansidman** · 05-27-2013, 08:29 AM

Suggest you post an example of what you are trying to do. Post what your data looks like, and what you would expect it to look like after extraction.

**adri_i** · 05-27-2013, 08:38 AM

I have a database like this:
year earning
2000 25
2001 50
2002 75 and I want to calculate the ratio earning in 2002/earning 2001 and the same for all the years. It is possible with query. I want to avoid bringing the data and then type the formula in excel.

**alansidman** · 05-27-2013, 08:57 AM

You will need to get the previous value into the current record. Than you can create an expression in your query to calculate the ratio.

Here is a link to show you how to do that. Scroll down to the section "Get the value in another record."

http://allenbrowne.com/subquery-01.html

**adri_i** · 05-27-2013, 09:06 AM

Thank you very much for the reply but for the moment I still don't know how to do it.

**alansidman** · 05-27-2013, 01:40 PM

Perhaps if you uploaded your data base, we could provide an specific response.

**adri_i** · 05-28-2013, 02:39 AM

My database file is like this:
Data cpi
2000 102
2001 103
2002 112
2003 123
2004 130
2005 135
2006 138
2007 142
2008 145
And I query the value cpi in another excel file. I want to calculate the ratio cpi value to cpi previous value (ex: 103/102 and the same for all the years). I know I can do this after I brought the data into my excel file, but I want to know how to do this in a query and have the data calculated in my new file.

**alansidman** · 05-28-2013, 07:23 AM

See attached. I was hoping that you would upload your database so that I would not have to recreate it. This is proper etiquette in Forums. Time is valuable for all of us. Help us to help you in the future.