Re: Make my formula copy across columns
The INDIRECT() method is probably best. you will need to build the ranges somewhere else (like above the cells), and then use the indirect where you reference the cells.
Re: Make my formula copy across columns
The INDIRECT() method is probably best. you will need to build the ranges somewhere else (like above the cells), and then use the indirect where you reference the cells.
Re: Max value from a graph
Do you have a trendline for this plot? Is the equation quadratic? Can you display the regression equation, then take the first derivative and solve for zero? I can't envision another scenario.
Please post an example if you can.
Re: Max value from a graph
Firstly, let's assume that your Y values are not multi-modal.
Next, let's assume that your X Values are in column C and your Y values are in column D
So, the max Y value is:
=MAX(D:D)
And the Max X value for that Y Value is:
=INDEX(C:C,MATCH(MAX(D:D),D:D,1))
Does this work for you?
Re: Replace Grid Lines
Jena,
gridlines are just a fancy type of border. You can add them to a range without the code:
1. select the range you want to add them to
2. go to format --> cells --> border
3. select the color in the bottom right of the first block (grey - 25%)
4. apply to the interior and exterior using the appropriate buttons
If you have to do this continually, you can use a module in the VBE editor, but you might not want to go the code route for just this.
Re: Changing formula based on drop down list
1. Use a drop down box to drive the value in a cell (the non-vba dropdown).
2. Use a lookup to decode the target cell into the region that you want to use in the sumproduct.
3. Drive the sumproduct off the lookup result.
This would be easier if you could post an example formula. Is Japan further segmented into cities, etc? You could also handle this w/o VBA, and that would be easier to update without having to go to the code when new breakdowns are added.
Re: Change negative numbers to positive numbers
Yes. In another cell, type in -1
Then, copy that cell.
Select the column that you want to change and go to edit --> paste special --> multiply
Hit OK.
That should do it.
Re: how to skip blank cells using a vlookup FORMULA
Can you be more specific about the data and what you want the VLOOKUP to accomplish? The title says you want to skip blank cells, but in the post you say you want the VLOOKUP formula to run only on blank cells.
Re: Need Help with VBA Array - For Each Running Slowly
Rory,
I had not thought about condensing to one line. I suppose I used the variable when I was writing the formula to make it easier for me. But, now that I have all the pieces I need, I can write it directly:
Public Function FRONGELLO(Current As Double, portreturns As Range, BenchReturn As Double, Previous As Double) As Double
'Thanks to Rory & GlennUK on Ozgrid for helping me sort this mess
FRONGELLO = (Evaluate("PRODUCT(1+ " & portreturns.Address & ")") * Current) + (BenchReturn * Previous) + Previous
End Function
Thanks for all the help. Hopefully I will be able to pay it forward one day.
Re: Need Help with VBA Array - For Each Running Slowly
Rory,
I was unaware that using a double would return a decimal. I had been having problems with my formula evaluating to an integer when I first wrote it, so I thought the variables were the problem. Using your tip, I have written it as compactly as I think I can. I went ahead and swtiched to a single, since these numbers are quite small, and I only have five decimal points of precision on the inputs anyway.
Public Function FRONGELLO(Current As Single, portreturns As Range, BenchReturn As Single, Previous As Single) As Single
'Variable declaration
Dim frong1 As Single
Dim frong2 As Single
Dim frong3 As Single
Dim myprod As Single
'Calls PRODUCT function to run against portreturns range
myprod = Evaluate("PRODUCT(1+ " & portreturns.Address & ")")
'Links prior periods to current period
frong1 = (Current * myprod)
frong2 = (BenchReturn * Previous + Previous)
frong3 = (frong1 + frong2)
'displays value of formula
FRONGELLO = frong3
End Function
Display More
Thanks to both of you for your help.
Any other thoughts would also be appreciated.
Re: Need Help with VBA Array - For Each Running Slowly
GlennUK,
Thanks for the advice. That has sped it up about 50%
New code:
Public Function FRONGELLO1(Current As Variant, portreturns As Range, BenchReturn As Variant, Previous As Variant) As Variant
'Variable declaration
Dim frong1 As Variant
Dim frong2 As Variant
Dim frong3 As Variant
Dim myprod As Variant
'Calls PRODUCT function to run against portreturns range
myprod = Evaluate("PRODUCT(1+ " & portreturns.Address & ")")
'Links prior periods to current period
frong1 = CDec(Current * myprod)
frong2 = CDec(BenchReturn * Previous + Previous)
frong3 = CDec(frong1 + frong2)
'displays value of formula
FRONGELLO1 = CDec(frong3)
End Function
Display More
Anyone have any idea about the decimal? I have read (and tried) to declare a variable as decimal, but that is not accepted, which is why I have the CDec() code in there.
Re: Adding a sheet and then referencing the new sheet in an existing code
Is the number of sheets to add undetermined? If so, why not something like
I'm a long time lurker, first time poster to Ozgrid. So, hello all, and thanks for all the help to date.
My question is around how to improve my code so that it takes less time to run.
I have a UDF that uses a for each statement to calculate an array. Here is the code:
Public Function FRONGELLO(Current As Variant, portreturns As Variant, BenchReturn As Variant, Previous As Variant) As Variant
'Variable declaration
Dim frong1 As Variant
Dim frong2 As Variant
Dim frong3 As Variant
Dim prod As Variant
Dim r As Range
'Set Array to 1
ArrayCalc = 1
'calculates array as {PRODUCT(1+periodic portfolio returns)}
For Each r In portreturns
ArrayCalc = ArrayCalc * (r.Value + 1)
Next
'Links prior periods to current period
frong1 = CDec(Current * ArrayCalc)
frong2 = CDec(BenchReturn * Previous + Previous)
frong3 = CDec(frong1 + frong2)
'displays value of formula
FRONGELLO = CDec(frong3)
End Function
Display More
I can accomplish this more quickly with an array formula in Excel, but then I lose the ability of the user to interpret the fact that this formula is a specific algorithm (known as the frongello alogrithm). Also, if a user wants to use the algorithm and this formula isn't defined, then they have to know how to use an array formula...easier said than done in this crowd...
Really, this code could probably be improved in two ways:
1) is there a better way to have the formula evaluate as a decimal? I realize that declaring my variables as variant is taking up more space than is probably neccesary, but I am not sure of the proper method
2) is there a more efficient way to write an array formula in VBA that mimics {=PRODUCT(1+"range")}
I have been an Excel user for a long time, but I am fairly new to VBA, so any help would be appreciated. I have been reading several programming guides to get up to speed (and Ozgrid obviously!)
--Jeremy