Excel Busn Math 57: Future Value Calculations

I have an idea in my head that I think might be useful, but it sounds like something that I would imagine would be included in the base excel program.

So before I start chugging through whatever code is in my head, I would like to present this example and ask if there was a simpler way.

I had a bunch of cells that listed the downtime for certain machines. At first the data was listed by decimal hours, but I felt like doing it by minutes would be more useful so I made a VBA program that went through each cell in a selection and multiplied the value by 60. Everything is nice and neat but that program was limited to just multiplication by 60.

Suppose I had a bunch of cells that I needed to be changed differently, perhaps in a rather complicated manner, and the needs of that change might be different as time goes on? I understand I could make simple changes to some VBA code (or a rather complicated use of relative references in a different worksheet and a values copy) but some people might need this function that have little or no understanding of VBA or the finer points of Excel.

So, my question is this: Can I have a selection of cells, specify one or more mathematical calculations, and have those calculations worked on each cell using the basic Excel interface or built in functions?

If not: I'm thinking of a program that will take in a string that specifies the mathematical calculations, do several searches in the string to find all the math operators and put all these locations in an array. Then chop up the master string, find the operators and do the calculations based on the rules of operator precedence. Probably include some userforms so the user can see what the program sees and intends to do.

Sounds complicated? Yes, but it should be a learning experience.

Thanks for your time

PegasusJF

Hi,

I'm trying to write some code to automatically calculate different math
functions based on a set of data. Namely: mean, std dev, and %rsd. The
calculations are based off of data that is imported and contains a variable
number of entries. The data is initially imported in a big unordered clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need to do
these calculations on each group of data. I have accomplished the grouping
by using looped counters that search for matches and then record the first
and last cell addresses to define the upper and lower limits of each group ie
A1:A21. This gives me the address locations, stored in variables, of the
data I want to calculate. My problem is all the examples of formula code in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my variables in
the equation they are not recognized due to the quotes. Can someone show me
the syntax to do what I need? I basically would like something like this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for example.

TIA,

Eric

Hello,

Say I have a MAX function which selects the MAX between 10 variables which are calculated in VBA code.

Ex:

In Year 1, the max function looks at those 10 variables, selects the one with the highest value.

I want the code to then exclude that variable for future MAX calculations as in in Year 2 the MAX function would select the maximum value between the 9 variables left and so on.

How would go about doing this?

Thank you

I have created a graphed curve in excel based on data I have entered. I now want to take the formula for the graph, and use it as an excel computation where I can plug in X and get Y, or plug in Y to get X. However, when i try to enter in variations of these equations, I end up with just the wrong answers (based on the data that went into the graph to begin with.

The formulas excel gives me are these:

y = -1.1288x 3 + 23.209x 2 - 166.76x + 430.07 (preferred), or alternately


y = 579.38e -0.574x


So how can I enter this formula so that I can have it pull x from a cell and spit out the answer for y (and the reverse)?

i am trying to find a formula to find a future vaule of an investment. i must use absoute and mixed cell references. when i enter the rate, the col. letter is variable but the row is fixed. when i enter the Nper, the col. letter is fixed but the row number is variable. and when entering the Pmt both the col. letter and row number are absolute.

C5=4%
B6=6

I want to create a function which defines a pulse wave that rises from
0 to 1, stays at one for a bit, lowers to 0, and stays at zero for a
bit.

/ if (t mod 1) <0.25, V(t) = 4* (t mod 1)
| if 0.5 >= (t mod 1) > 0.25, V(t) = 1
V(t) = |
| if 0.75> =(t mod 1) > 0.5, V(t) = 1-4* (0.75-(t mod 1))
\ if 1 > (t mod 1) > 0.75, V(t) = 0

For this pulse wave there are straight rises, falls and flat bottoms
and tops, which I need (and which is why a sine wave can't be used). If
you're still with me, thanks.

I can either put this all into a long excel equation for each cell that
I want this in (there will be about 4 'if' statements and 6 'mod'
statements) or create a UDF, where the t mod 1 only has to be
calculated once.

Any idea out there how much faster/ slower as UDF really is in a case
like this? Also, can you think of an easier way to create this pulse
wave?

As a side note, the t in my program has some ROW() and COLUMN()
dependence, and there will be 200+ cells filled with this equation
(thus, I can't just compute it once and copy the values to each cell).
Caculation speed is important because each cell is being calculated as
the time, t, is advanced by a timer.

Thanks in advance to anyone who takes a stab at this.

-Abe

Hi, I'm trying to enter in the current days date and then from there have the ability to automatically drag out the formula or create a formula that will depict future and previous WORKDAYS only (monday through friday). I know how to do it manually by implementing the "=Today(),+1)" type formula, but I'm looking for something that is going to prevent me from manually entering that Data, and will allow me to drag the cell out to depict future and previous weekday dates. PLEASE, can someone help me, as this has been occupying me for the past few days and I need help!!!

I need a formula that does this. I have a base number of 300 seconds and want to add 30 seconds to it for every number over 1 so the output would be like this
input 0 and get 0 out
input 1 and get 300 out
input 2 and get 330 out
input 3 and get 360 out
input 4 and get 390 out
and so on. I can't for the life of me get the math down. Can someone please help.

DATE SPORT TEAM [B] [A],,[C] SPREAD MONEY LINE WAGER RISK TO WIN W/L/P DAILY PROFIT 3/8/11 NBA LA Lakers [A] -4.5 -110 $100.00 W 3/8/11 NHL Ottawa [A] +1.5 -120 $100.00 L 3/8/11 NHL Montreal [B] ML +140 $100.00 W

Guys i need your help. On th above example is a betting spreadsheet i am putting together. I want to enter the information above and have excel fill in the "risk" and the "to win" automatically by using the money line. I do not know how to get the number. If it is a "-110" or any other negative number it would be the wager*1.(what ever the number is...i.e. -140, -120, -175 etc). BUT the problem is what we call the "take back" if the number is +140 and you wager $100 and win you get $140.

So i just want to enter the moneyline and wager amount and have it figure risk and to win. And not to be greedy but is there is a "W" "L" or "P" i would like it to go into the daily profit column.

W=money won from the to win column
L=money lost from the risk column
P=nothing lost or won.

This is what i hope it to look like but with entering only the information from the wager column

DATE SPORT TEAM [B] [A],,[C] SPREAD MONEY LINE WAGER RISK TO WIN W/L/P DAILY PROFIT 3/8/11 NBA LA Lakers [A] -4.5 -110 $100.00 $110.00 $100.00 W $100.00 3/8/11 NHL Ottawa [A] +1.5 -120 $100.00 $110.00 $100.00 L -$110.00 3/8/11 NHL Montreal [B] ML +140 $100.00 $100.00 $140.00 W $140.00 Total: $130.00
L
I hope this makes sense. Thank you for your help.

Need help to create a formula that will, first multiply the value of two cells, then divide by 3600 & multiply the answer by a certain percentage depending on which range the answer falls into. eg.
If 2100 (A1) x 210 (B2) divided by 3600 falls between 1200 & 1400 then multiply by 110%, if answer falls between 1000 & 1200 then multiply by 112%, if answer falls between 900 & 1000 then multiply by 114%.
Thank you in advance for any assistance.