Making calculate formula.

I'm a little rough with my VBA skills but Sounds like just a general formula would work in this instance. Is there any reason you would need VBA to calculate this for you instead other than using this as more of a learning tool in using VBA?

Using VBA to calculate is just a matter of labellling and snagging the information from the cells you want and putting it into a formula and spitting it back out.

Example:

I have a kid who bowled a 100 on his first game (put in cell A2), 127 on his second game (put in cell B2), and 98 on his third game (put in cell C2). I would like to know his average (will show in D2).

Please Login or Register  to view this content.

of course we could just simplify this code into one line.

Please Login or Register  to view this content.

This is some of the funner things in VBA. I've seen some pretty funky formulas out there and some that I've created have almost taken me one hour just to get the formula down right.

For this you need to understand the basic principles of algebra when it comes to creating the formula or summation. Especially when it comes to mixed symbols such as divide and multiply. I hope I'm not dumbing this down too much as I have no clue what your math background is.

The first point you have to find out is if you are dealing with a set amount of inputs or a variable amount of inputs.

A set amount would be like having 2 cars and you want to know how much you pay on average for gas between the two. 2 will be your set amount cause it won't be any different.

Now if you wanted to figure out your gas mileage over a period of time that would be a variable as the numbers you would be needing would be constantly increasing.

So for now if you stick with set amount of numbers it's just a matter of collecting the data and then putting it into formula.

A = Range("<Cell #1>").value
B = Range("<Cell #2>").value
etc......

Once you collect all the data you can then plug everything into your formula. Remember that (Parenthesis) always figure themselves out first before working itself into the next part of the equation.

Example:

A = 2 + 2 / 2

Now normally if you worked this out you would have to do the division first, then the addition, so A would come up as 3. 2/2 = 1 so A = 2 + 1 and so A = 3.

Now add the ( )

A = (2 + 2)/2

A is now = 2 cause the items in ( ) had to work themselves out first before continuing.

So for larger formulas you would have to work from small to big. Scratch paper is always your best friend.

As for items that have a variable set of inputs we would have to figure out how and where those inputs are introduced then we could write a macro to calculate them. Most data over time segments are ran in a straight column or row so we should be able to do most calculations just using a Do/Loop statement. If they are in different places then the macro will get more complicated.

So if we bought a new car let's say a year ago and when we bought it we wanted to keep track of how many miles we average per month. We can set up a macro to go through and do that for us. We would have to know what factors to collect.

• Total amount of miles
• Total amount of months
• How many miles did we start with (We'll say it had 53 from all those test drivers).

Next step is figure out where we have those factors and how they were entered. Let's say we just started putting the ending mileage of each month in column A (Starting at A1).

So now we have our mileage and our hidden information is the amount of months that is covered. Reason why I say it's hidden is because we don't go and tell it exactly how many months total. Instead we have to include in the macro something to count how many months.

Now that we're collecting all that data we now have to make it known to us. So we put it all in the formula or have calculate along the way. Since this one is a running sum we'll calculate on the way and then use a formula at the end to find our average.

Now let's create our Variables. I always like to use variables that remind of what I'm working with. So we'll use Mi for miles, Mo for months, and Ma for the average. Here is the macro I would create.

Please Login or Register  to view this content.

If you put this into a macro on a spreadsheet and then use F8 to step through it you can watch as it will loop until it finds a blank cell then puts our average in cell C1.

As you can see the formula has mixed set and variable numbers. The variables we picked up along the way and the set number was given to us at the beginning of our planning process (The 53 miles already on the car).

The Mo is only counting up by one because we should only have one input per month (end of the month entry) on the car. The Mi is obtaining the last entered amount. Notice how is doesn't have any mathematical feature? It's basically grabbing what is in that cell so the last one it gets should be the total miles that the car has been driven.

To find Ma we take the Last set of miles (Mi), minus the 53 it started with, divide it by the total months (Mo) and there we have our average per month (Ma).

I hope this helps you understand the process of formulas in VBA. If you need something more advanced than this please give me a good example of what you would like to see.