This is a tricky question...

I have a database that is passing values to a web page. These values are then put through a series of calculations to determine 12 new variables.

These variables will appear only if there is a value to them. this is how it works.

One page is used to display data about a country. There are 185 countries in the database. In certain cases, there is no data for a particular country. In other cases, there is.

So country A has variables that show up from variable1 to variable 12.

Country B, however, only hads values for variable1 to variable6.

I need a calculation that will establish the average for country A's variable1 to variable12 by taking all these variables and dividing them by 12.

The same formula has to be able to take the values of country B and add up variable1 to variable6 and divide it by 6.

In Excel, you can make such a formula. Is there a way to do it in VB script? Or will I have to create a gazillion conditional statements?

Thanks for the help.

You can still calculate the average by using the 12 variables. Just set those (7 - 12) equal to zero...calculate the average...multiply that by 2.

1 + 2 + 3 + 4 + 5 + 6 + 0 + 0 + 0 + 0 + 0 + 0 = 21
21 / 12 = 1.75
1.75 * 2 = <b>3.5</b>

1 + 2 + 3 + 4 + 5 + 6 = 21
21 / 6 = <b>3.5</b>

QED. The answer is the same. Hope this helps.


Xcalibur
Cape Town - South Africa

Edited by - Xcalibur SA on 01 Dec 2003 09:40:32

If it was that simple, that could work. Unfortunately, there are 185 countries and the data is updated on a monthly basis. Most of the countries use the 12 variables but some have 6, others have 8, and still others have 11.

That's why I was hoping there would be something similar to the averaging formulas in Excel.

What are you using for the database? There should be a way to calculate it through the database before you call it to the page.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
What are you using for the database? There should be a way to calculate it through the database before you call it to the page.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I'm using Access 2000 but am no expert with it. The database has roughly 20-25 tables in it. But the values used from the equations would come from the same table, if we went that route.

The reason I was using the variables the calculations were being done already on the table. Here's what it is:

I have a table that displays the currency value of a country vs. that of a country selected by a user through a session variable. The values are displayed for the previous 12 years.

Because I'm using the U.S. dollar as the common denominator for all countries, calculations are being done to create a variable that compares the two currencies to the US dollar and then does the proper conversion. That part works fine.

The next table row has the devaluation/appreciation of the currency vs. the country selected since the previous year. Again, simple calculation.

Here's where I run into trouble. The next table row is the appreciatin/devaluation of the currency between 2003 and the base year in the table. That means that all cells up to that year have to be averaged out.

The problem is that some countries were not in existence in 1993 or did not have their own currency (i.e. former Soviet bloc republics). Some (such as Tajikastan) did not get their own currency until 1996. Using "if...then" statements for those years should resolve that.

But in another table, I calculate the rate of inflation and have the same calculation between the country's rate for the current year and the base year selected. This is where I run into real trouble because some countrie (such as Afghanistan) have no data for recent years (1997 on) but have them for years previous to that. My formula must take into account that there are no values for the last five years and deduct that from the divider.

In Excel, the formula looks like this:

<pre id=code><font face=courier size=2 id=code>=IF(I60="-","-",AVERAGE($D60<img src=../images/dmxzone/forum/icon_smile_blush.gif border=0 align=middle>60))</font id=code></pre id=code>

(That smiley should actually be ": I60" without the space)

with I60 being the cell where the value is the base year and D60 is the 2002.

Is there a way to translate that formula into access so that all countries can use the same formula? Or will I just have to build in exceptions for those countries that are not using values for recent years?

BTW, the page I'm developing has sooooo much data being pulled from the table that I'm also concerned about the loading time. There must be 30-40 recorsets being used but that's what the client wants...I've broken some of the displayed data into SSIs so that the actual page isn't using like 3000 lines of code but all the data is being pulled into that one page.

Edited by - cbeaudry on 03 Dec 2003 19:26:27