Excel Statistics 14: Categorical Relative & Percent Freq. Dist. w Formulas

Hi,

Just a quick question.

I know that you can record a macro in relative mode, but I am uncertain if excel has built in formulas (worksheet functions) that can do calculations in relative mode. Say a v-lookup that automatically selects the correct data range for a prespecified column.

v-look up is just an example, but I am wondering the following: Do front end relative formulas exist? If so where can I find a list, so i can look them up in excel help wizard Can anyone recommend a book that focuses on relative formulas (assuming they exist)? Thanks in advance

Matt

I am drawing a series of chart with x-axis as time and y-axis as deformation (say of a slab). Chart type is x-y line. I got a chart with x axis as equal division of time. Now I want a secondary x axis which gives the categorical value, the days position (provided in the input) on which the deformation measeured.
In short, I want a secondary x axis which shows the categorical value on the primary value axis??

Any one have the answer, plz post.

Raj

Hi,

Does anyone know a formula, or any other way to generate a random number from an exponential distribution with a mean of 2?

Thanks!

Jim.

1. Select a range of cells in a sheet, and press Ctrl+C.
2. Select any cell in the sheet, right-click, and then select Paste Special from the shortcut menu or select Home -> Paste arrow (in Clipboard Group) -> Paste Special.
3. In the Paste Special dialog box, select Transpose, and then click OK.
4. The Transpose option pastes the data in the opposite direction (that is, horizontally to vertically, or vice versa).





You cannot select the Transpose option together with the Paste Link option. In other words, you cannot create links while changing the direction using Paste Special.




1. Select the range A1:B5, which has the range Name Table defined for it. The size of the range is 5Rx2C, that is, five rows by two columns. (To quickly find the measurement, select the range and watch for the size in the Name box while holding the mouse button down.)
2. Select a range of 2Rx5C starting from cell A8, that is, the same size as the range Name Table but in the opposite direction.
3. Type the formula =TRANSPOSE, and then press Ctrl+A.
4. Press F3, paste the Name Table, and then press Ctrl+Shift+Enter.

1. Select the range of cells containing the formulas and press Ctrl+H or
select Home -> Find & Select (in Editing Group) -> Replace to open Find & Replace dialog box.
2. In the Find what box, type the equal (=) symbol.
3. In the Replace with box, type the # symbol (to change the formulas to text).
4. Click Replace All, and then click Close.
5. Copy and paste the cells to a new location.
6. Repeat steps 1 through 3 while reversing the # and = symbols (to change the text to formulas).

I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly. Is there anyway to get around this?

I have a set of macros in a template/starter file for users to open and start a project. Within this workbook contains a bunch of macros. Instead of sending everyone a new workbook with updated macros each time, I would like to host these macros or somehow distribute updated macros to everyone efficiently.

I am unsuccessful in finding a way to have a button call another macro from a workbook on a network drive.

I am also unsuccessful in using an addin. I have saved the macros as an add-in and added it to my workbook, but am unable to call those functions.

I could also just export the modules each time one updates but then I have to rely on the user to replace their current one which I don't want to do.

Essentially, I would like the update to occur without relying on the user to do very much or anything at all.

Thanks

This may sound simple, and I was hoping I could find the answer thru searching the forum but its giving me limited results (im still getting used to the new layout!) Anyway, there are two formulas that I need. One formula is getting the dollar amount of the percent given: for ex:
column A has the cost ($15.95), column b is the percent (8%). column c = is where i want the dollar amount of 8% of 15.95

problem no. 2
instead of the percent provided, it is the dollar amount, in column c I need the percent of the dollar amount given.
ex:
column A ($6.94), column b is .14 what is the equivalent percent of .14 in 6.94

Hope I didn't confuse you guys. Id really appreciate any help

Hi All,

I trying to find (or alternatively write) functions that calculate mean and variance for bivariate probability distributions.

For example:

Skis
Boots 0 1 2
0 0.08 0.14 0.12
1 0.09 0.17 0.13
2 0.05 0.18 0.04

I wrote my own function for a single variable probability distribution, but this is more complicated since you need to sum rows or columns depending on the variable you are calculating for.

If I have to write this, I guess my first question would be how can I sum up either a row or column from a range and store the solution within an array?

i.e For column(i)
Sum all of the row values
Store in array(i)
Next column

Any suggestions on how to do this, where I can find something that already exists, or how to best approach this in general? The help of this forum is, as always, greatly apreciated.

-Zack

Hello,

I have the following code:-

Code:

    Dim EndRange As Range
    Dim StartRange As Range
    Dim FormulaRange As Range

Range("B65536").End(xlUp).Select
Selection.Offset(-1, 0).Select
Set EndRange = Selection
Set StartRange = Range("B7")
Range(StartRange, EndRange).Select
Set FormulaRange = Selection
Range("B65536").End(xlUp).Select
ActiveCell.Formula = "=SUM(" & FormulaRange.Address & ")"
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.FillRight

This produces absolute formulas and I would like relative. How do I do this?

Kind Regards
Matt