Excel Busn Math 58: Present 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

The following code fills down column B for rows 3 to 110, regardless
of the inserted "If Not IsEmply' statement.
I've got formulas in Column A from row 3 to 110, but visible values in
rows 3-5. I want it to fill the for the visible values only.

Code:

Sub CopyTest()
Dim OutPL As Worksheet

Sheets("Pull").Activate
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
 For i = lastrow To 3 Step -1
   If Not IsEmpty(Cells(i, "A")) Then
      lastrow = Cells(Rows.Count, 1).End(xlUp).Row
   Set OutPL = Sheets("PULL")
OutPL.Cells(i, "B").Value = Sheets("Bill OF MATERIAL").Cells (4, "D").Value
     End If
          Next i
                    End Sub

Any help is appreciated.

Thanks,

BDB

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 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

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.

Is is possible to do complex math in Excel?

I can represent a number as a complex using the "=COMPLEX(A1,A2)" command. But I would then like to add, subtract, multiply or divide the complex numbers.

Thanks,

Raihnman

I am using Excel to balance my checkbook. How do I enter a debit or credit in one column and then have the next column automatically display the new balance?

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.