Posts by Anonymous

    Re: Add Total Columns Through VBA

    Try the following code modification to one of the Subs. I'm unsure that I understood what the Present Total should count, so if I have that wrong please explain what it should count.

    Re: Calculate Current Y-T-D variance from Last Y-T-D figures

    G15 is easy. Just use =SUM(G3:G14)

    For H15 you will need =IF(F15="","",G15/SUMPRODUCT(E3:E14,SIGN(F3:F14)))
    where SUMPRODUCT(E3:E14,SIGN(F3:F14)) is one way to get the appropriate sum from the E column.
    You could instead use SUMIF(F3:F14,">0",E3:E14) for that total.

    Re: vba create pivot table

    If you want to see where your original code went astray, then turn error checking back on and see what error messages occur.

    Re: Macro for Printing Excluding Formulas

    Try the following to hide the lines. If it works on your data, then you can add back the other parts.

    Re: Tricky Percent Growth Problem

    A Final time. Read your objective again. You are looking for a growth profile that matches the profile of the prices. The prices in this example do not increase by the same amount each month. To match their profile use Column D.

    Re: Tricky Percent Growth Problem

    Here is an example that may help. Suppose a stock starts at 1 and doubles every month, so its monthly values are 1, 2, 4, 8.
    Then the monthly growth each month is 100% and the cumulative growth numbers are 100%, 300% and 700%.
    Note that the differences in the monthly values of the cumulative growth can exceed the largest monthly growth rate.
    Does this help?

    Re: Tricky Percent Growth Problem

    I'm sorry, but we both seem to be repeating ourselves. The difference in Cumulative growth between two months is not, in general, the same as the growth from one month to the next If you want that to be the case then you would need cumulative growth to just be the sum of the individual monthly growths, which is not the standard definition.

    I'm afraid I can be of no more help on this subject

    Re: Tricky Percent Growth Problem

    The logic you have been using to calculate the cumulative growth is correct, The logic you are using to doubt its correctness is faulty.

    Cumulative growth in cell C464 should use the formula
    where cell B217 has the original value and B464 the current value. (The ^(1/1) term you are using now is superfluous.)

    Re: Tricky Percent Growth Problem

    The formula as written goes in cell D464 of Post Number 6.

    Maybe it's time to pause and ask what results you really want to have. What is the purpose of this workbook? What question(s) is it trying to answer?

    Re: Tricky Percent Growth Problem

    I think you are OK as the workbook stands (although the ^(1/1) term is unneeded and has no effect). Column C calculates the change from the previous month. Column D from the initial value in row 217. The differences in column D have no direct relationship to the values in column C because their base (the denominator) is different.

    Re: CountIFS greater and less than statements

    When you say COUNTIFS($D:$D,U$1,$K:$K,"<>12345") doesn't work, do you mean it gives the wrong answer? or that it gives an error?

    Try the equivalent formula

    =COUNTIF($D:$D,U$1) - COUNTIFS($D:$D,U$1,$K:$K,"12345")

    Does this give the correct answer?