Posts by HarryFrohlich

    Hi everybody!

    Everything that I searched ended up not answering my question - or even not doing what I wanted at all.

    My question is very simple: If I have a cell filled with any number of given names (no surnames) (e.g. John Peter William Suffolk - yea, 4 names!, or Harry Murray (2)), how can I extract the first letter of each name (i.e., the first letter following a space) and place that in another cell?

    Say A1 has the names, B1 has the surname - I want this in C1.

    In D1 I'll use a function like Text or concatenate to link C1 and B1.



    Re: Code Or Formula With Which To Detect Ip Address?

    Nice one, Bob!

    They are, indeed! I simply combined the values in the array with=A1&A2&A3 etc and the combined IP address then looks exactly like the one the button provides.

    Great work!


    Re: Code Or Formula With Which To Detect Ip Address?

    Thanks Andy! When I did the "array" thing, all cells showed the same address.

    And thanks Bob! When I did yours, only the first two cells had values when I entered it as an array - and the values differed.

    Does it mean that I have two IP addresses?

    Strangely enough, I'd just visited a website, and they said: "Welcome to WebmasterWorld Guest from" - completely new format and not at all the same IP address that GetIPAddress.xls provides! Go figure!

    Thanks for your help, guys!


    Re: Code Or Formula With Which To Detect Ip Address?

    Hey Bob and Andy!

    Thanks for the code for the function, Andy.

    When I'd looked at the code, I could see that the eventual code would be in the section you used - but I couldn't find the way the actual IP addresses were found. I still cannot!

    Bob, when you said


    Its all in the GetIPAddress.xls workbook, so just pluck it out and add it to yours.

    , I thought that you meant that I should find the SECTION that I require - until I copied Andy's code to a new workbook and the thing didn't work. Only then did I realise that you said that I should copy ALL of Rob's code and paste ALL of the code in my workbook!

    That didn't answer my question though :wink: - I needed an answer like ol' Andy gave.

    But Andy, I understand from Rob's write-up in the code that a computer can have more than one IP address. Mine only has one, and so I cannot see what your function does -

    does it actually provide more than one (it does not look like it)?
    does it write all of them in one cell?
    why does the answer of your function differ from the button's? (Your code has 123.456.78.9 and the button declares it to be 123.456.78.9101.234.5.6) Your's is the standard I recognize and it seems to be a truncated version of the one provided by the button.

    Thanks for helping!


    Re: Code Or Formula With Which To Detect Ip Address?

    Sheesh! That was quick Andy! Thanks, man!

    I looked at, then through, the pages of code, then searched what I thought to search for, and eventually thought to just ask:

    How can I get the IP address written to a cell in Excel, please?

    If there are more than 1 address, can each be written to the next cell down?

    It's not as though I am asking this just of you, Andy, but I am sure you'll be the one I'll be thanking again!

    Best wishes


    Re: Calculate Balance To Zero Based On Constant Fix Rate & Withdrawals

    Hi Derk!

    I am just as happy! :) And I am happy to hear from you, too!

    For interest's sake, I also realized that VLOOKUP will do the exact same thing with one less column. Use VLOOKUP to find the actual value of the cell which should be ZERO before Goalseek is run.

    I keep quoting Mr Einstein about KNOWING versus UNDERSTANDING, and the latter always seems to open doors in sticky situations, eh?!

    If I may, allow me to explain the logic behind my reasoning:

    Step 1: In a specific column, one specific cell needs be forced to be ZERO by changing the value of another cell.

    Step 2: How do I actually know which cell needs to be forced to be ZERO? In this case, this will happen one step before the answer to one value deducted from another becomes negative.

    Step 3: Is there a formula that I can type into a column that will flag this spot? Yep, the IF function. Copied down a specific column, I simply said that the IF function should return an answer of 0 if the answer to the one value deducted from the other (Step 2) is positive but, as soon as the answer turns negative (actually, just before it turns negative), that it should add 1 to the value of the cell above it. This will then have a lot of zeros and suddenly the figures 1, then 2 etc. 1 flags the spot I need as far as the row is concerned.

    Step 4: Now, because this 1 will go up and down the column for different scenarios (moving the row where the one value deducted from the other becomes negative), how do I know which row to target with Goalseek? I stopped worrying about that when I realized the following: By having a FIXED cell equal to the "roving" cell, I need not have Goalseek worry about a moving target. OK, so how do I get the value in another cell equal to the value of the moving cell? Use VLOOKUP! In the VLOOKUP function, I'll always target the value 1 in the column with the IF function discussed in step3 and reference the value I am looking for within the IF function.

    Step 5: Type this formula in any cell whatsoever. This cell with the VLOOKUP function will always be equal to the value in the moving cell simply because it displays the actual value as it changes. If this value in the VLOOKUP cell is not zero (I need it to be zero, remember?), I simply have to instruct Goalseek to make it zero.

    How would making the value of the VLOOKUP cell zero get the other one to be zero? Simple. Because the value of the VLOOKUP cell is determined by the value of the actual moving cell, it follows that the moving cell also HAS to be zero before the VLOOKUP cell will return zero.

    In the supplied sheet I used OFFSET, which basically also "mirrors" the value of the "target cell".

    I do not think I'll forget this solution soon . . . (I hope!)


    Hi all!

    I cannot seem to find what I am looking for when searching so I'll post my question.

    I have a situation where I have to determine the required size of a deposit from which constant withdrawals are to be made until the deposit runs out to ZERO. The deposit earns interest at a fized rate, capitalized monthly after each withdrawal had been made.

    In my example case, the target month (The actual cell that needs to have a value of ZERO (when the deposit runs out), or the "Range") is found in cell G16. In another situation, it may be found in cell G19 etc, depending on the situation.

    It is this changing of the target cell (the one that needs to be ZERO) that has me stumped. I've been thinking of using VLOOKUP (not trying to lead you ;-)) to determine the actual position of the cell that need to have a value of zero but do not know how to build this into the VBA code of Goalseek.

    This target cell in the Goalseek code should be the cell in column G opposite the figure 1 in column A.

    In my example I simply typed in a figure 1 in column A - in the actual spreadsheet the position of this figure is calculated with a formula and its position moves from situation to situation. If the 1 is in cell A19, the target cell, whose value should be ZERO, will then be cell G19 - hope you get it.

    Can someone please help me with VBA code for Goalseek, please?

    I attach my example, including the recorded code for Goalseek.

    Kindest possible regards

    Harry[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I made some serious mistake with the code of the macro in the previously attached workbook and attach the correct workbook marked as Goalseek2.xls.

    I also changed the structure of the worksheet so as to clearly indicate why I HAVE TO HAVE AN ANSWER TO MY GOALSEEK question. I appreciate the change of my title, but it does not convey my question accurately. By making the changes I suggest in cell D in the newly attached workbook, you'll see the affect in the workbook. The cell to which Goalseek has moved and I need Goalseek to see this and apply its calculation to the newly identified cell.

    Please change the topic back to asking help for Goalseek when the target cell moves around.

    The calculation of a value to zero is not my problem - the moving cell is.

    I HAVE TO HAVE an answer to my Goalseek question - unless I am mistaken and the new title will get better answers.


    Harry[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I answered my own question - eventually. I used OFFSET and one more formula which provides a value to be used within the OFFSET function.

    See Goalseek3


    Re: Jump To Next Cell After X Seconds

    Hi Dave and Readify!

    Thanks for your willingness to play with me (again) - and thanks for the code!

    Dave, your code allows input to be made when the cell is active, but nothing is entered in the active cell while it is active. Also, the active cell does not jump to the next when Enter is pressed. When the code has run its course, all the entered values are shown/entered one below the other starting at the actual last cell without giving me an opportunity to enter a value for the last question unless I increase the named range with one cell.

    Readify, you've gone to great trouble - thanks! I like the correct/incorrect marks!

    When running your code, I have the following "problems":

    While your code allows values to be entered - and also shown immediately in the active cell - the active cell can be manipulated by selecting any cell outside of the code-highlighed-cell. Also, when "Enter" is pressed, the code does not jump to the following cell (same as Dave's).

    I truly have no idea where to even start looking for a solution with the two sets of code I have and appreciate any further assistance!

    Kindest regards

    Harry[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi all!

    Am I to assume that I have all of you stumped?

    Any help?


    Hi all!

    I've decided to pay my son to learn his tables (2x3=6 etc) seeing as though the little blighter finds anything else better things to study . . .

    I thought to do the following:

    1. Create a worksheet for each set of tables (Sheet1: 2x table, Sheet2: 3x table etc)
    2. In field A3 (e.g.), I'd type (Formatted as text): 2x1=
    3. In field B3, he'd have to type the answer. To prevent him from using the cell as a calculating tool, it will be formatted as text (courtesy of
    4. He'll start the test by clicking a button that says "START THE TEST" (or something more exciting, like "Earn some money!"). I'd like a macro to select the first cell (This I can do, believe it or not! :-)) and then, after, say 5 seconds, jump to the second cell, then the third etc up to the last cell (2x13=)
    5. Despite the timer, the active cell must be allowed to be changed to the following one (one down) by pressing "Enter" (In other words, if the timer is set at 5 seconds and Jonathan enters the answer in 1, he may then press enter and move to the following cell immediately)
    5. He can only enter the answer in the cell while highlighted and I'll have the whole sheet protected once the macro has done its run to the end or once he's answered the final question and pressed "Enter".

    I'd appreciate help on the code needed to do the timer thing and the jump to the next cell after each time limit is reached or "Enter" is pressed.

    If possible, I'd also like to be able to change the time (in seconds) through an input box before the whole thing is run (Before the the "Earn some money!" button) and not by changing the code.

    Also, I'd like a seperate timer to run and display the "actual total time" (In a cell on the worksheet) it took him to complete the test (In other words, it should start timing once the macro is hit and stop when the final enter is pressed or the time delay has passed)

    Thanks in advance!

    Best regards


    Re: Calculate Future Value Of Monthly Recurring, Annually Increasing Payments

    Hi Aaron!

    Sorry about the delay - my family and I had a bit of a break.

    To answer your questions - yes, it's something for my work as a financial advisor. I confused myself with "effective" and "nominal" when I tried to figure out which was which when ANNUAL installments earned ANNUAL interest. I eventually figured out that the two are effectively the same simply because the installment periods and the interest payment periods are the same. All that matters is whether the installment is paid in advance or in arrears - but that still does not affect "effective" or "nominal".

    OK, so once I'd done that, I also realised that the interest earned at the end of year 1 will, at the end of year 2, earn interest on said interest, but that still did not change it to an "effective" rate simply because interest was earned on interest BECAUSE the period of the investment and the rate at which interest is returned still stayed the same at 1 year.

    Now, when monthly installments were to be made and an annual interest rate has to be capitalised monthly - that's where the issue arose. I realised that the monthly rate referring to an annual rate of 10% could be shown as either:

    =(1+10%)^(1/12)-1 (The answer to this is an EFFECTIVE monthly rate of 0.797% and an EFFECTIVE annual rate of 10%. The NOMINAL annual rate equals 9.57% (being 0.797 * 12))

    This annual rate of 9.57% is not what I wanted and I realised that I had to work with a NOMINAL annual rate of 10%.

    So, the answer became simple - I have to use the "dumb as a fox" method of paying 10%/12 interest per month (0.833%), thereby ensuring a NOMINAL interest rate of 10% per annum. This equates to an EFFECTIVE annual rate of 10.47%. (=(1+0.833)^12-1)

    Be that as it may, Dave Patton also, in the mean time, had realised that the formula he'd given me assumes that the payment increases EACH MONTH and not only once each year.

    This means that Dave Patton's solution with the single cell is not what I need. He advised me to stick to a spreadsheet system like the one you'd provided, Aaron, or the one I'd personally devised.

    So please (and I hope people read this warning!), do not use Dave Patton's formula - use Aaron's spreadsheet!

    Thanks Aaron! Hope you have a great weekend!


    Re: Calculate Future Value Of Monthly Recurring, Annually Increasing Payments

    Hi Aaron! It's been a long time! Nice of you to drop in!

    Thanks for the thoughts, but I really want a single cell formula.

    I have been using a different tack (see the sheet attached), and although I reach the same answer, my method is much less user friendly. I amended yours to reflect monthly installments in a table but something bothers me about the answers I get. If I calculate the monthly interest using the formula =Interest rate/12 (turning the annual nominal rate of 10% into an effective rate of 10.47% (=(1+10%)^12-1), my answers come out wrong.

    It does, at least initially, confirm my assumption that this investment should not do as well as one where the total premiums for the year is invested in month 1 (as with your example) and where all the interest is earned on a bigger sum of money right from month 1. In your sheet (as in my example), you (we) make use of effective rates of only 10% - and immediately I am comparing apples to strawberries, or nominal rates of 10% to effective rates of 10%. Not right.

    Eventually, in year 18, the monthly one has a bigger fund value than the annually in advance one - and this cannot be right.

    So I then changed the formula in cell I3 (copied down) to =(C3+F3)*((1+H3)^(1/12)-1) to use the monthly nominal rate of an effective rate of 10% per annum. This showed that an investment made with monthly premiums, paid in advance and earning an annual effective interest rate of 10% does, indeed, deliver less than one where the payments are made annually in advance. This at least compares one scenario of an effective rate of 10% per annum with another of the same effective rate.

    Still, I have no idea whether my formulas are correct or not and whether the answers are correct or not!

    And I still want a single formula!

    Thanks for the pointers, Aaron. I do, still and however, need confirmation that my answers are correct even if you cannot help with the single formula.

    Can you do that, please?

    Best regards


    Re: Reason For Double Negative Signs In Formula

    I don't know how I got this thing stuck as a sticky but asked Dave to rectify my clumsiness!

    h[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi Dave!

    Thanks for the tips. You may remember from a previous post that I actually mentioned that I'd like to study PivotTables and Sumproduct.

    And I remind myself yet again!

    I hope you can unsticky this for me - and thanks in advance!


    Re: Reason For -- Sign In Formula?

    Hi Turtle44!

    Thanks for the reference.

    The SUMPRODUCT function is clearly something I am able to use in a limited way only. I still do not understand what the "coerces true/false values to 1/0 values" actually entails but do not really have the time to sit down and figure the actual formula's requirements out right now. I bookmarked the page and will get back to it sometime. I suppose it does not look too daunting now that I know that True will be turned into a 1 and false into a 0.

    I even noticed that typing the -- in front of two IF functions such: =--(if(A1>10,true,false)+if(A2<100,true,false)), that the true/false also turned into either a 1 or a 0.

    A light has gone up!

    Thanks Turtle44 (and the others in the other post(s))

    P.S. You're right about doubting that I'd ever seen it in such a formula, btw. I simply used it to show that two minus signs, in standard maths, does something completely different.

    Hi everybody!

    I've just seen a formula with two - (minus) signs right next to each other placed before a section in a formula while reading possible answers to my post. Here is the link:

    I've seen it before and am intrigued as to why it is there and what it actually does.

    Assume it is used such: = --(1+2). Would the answer then not be =--3 = 3?

    Why not just leave the -- from the formula?

    Still wondering . . .