1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: (Excel '07 VBA) Trying to create a custom function

Discussion in 'Business Applications' started by Edoardo, Apr 21, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Edoardo

    Edoardo Thread Starter

    Joined:
    Aug 18, 2004
    Messages:
    44
    Hi techsupportguys,

    I have been trying to create a variant of the PRODUCT function (multiplies all numbers given as arguments) in Excel 2007, so that I "add 1" to each value before multiplying.

    So the formula would be Product(x+1) for a series of values.

    e.g. (0.32 + 1)*(0.44 + 1)*(1.22 + 1)* ...


    I have not been able to do this as I am new to creating macros - but I imagine it is really simple to do - I just don't know how and it is driving me crazy! It would be really great to create a simple function that does this (or even find a way of editing the PRODUCT function in VBA)

    Could someone please show me how it is done?

    Many thanks,

    Edoardo

    P.S. I know the roundabout way of doing this is to create a new column that adds 1 to all the values, and then calculate the PRODUCT, but it would be really nice to create a new function that does it. ;)
     
  2. LauraMJ

    LauraMJ Administrator

    Joined:
    Mar 18, 2004
    Messages:
    13,300
    Opened as requested.
     
  3. Edoardo

    Edoardo Thread Starter

    Joined:
    Aug 18, 2004
    Messages:
    44
    Thanks :D

    To summarise, the formula I am trying to create is

    Product(CELL VALUE + 1)

    Is this even possible using macro?
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi, if I understand you correctly, maybe this will work, assuming your data is in range A1:A3...

    Code:
    =SUMPRODUCT(A1:A3+(LEN(A1:A3)>0))
     
  5. Edoardo

    Edoardo Thread Starter

    Joined:
    Aug 18, 2004
    Messages:
    44
    Thanks for the response Zack but that doesn't quite achieve what I'm trying to do. Sorry if I'm not explaining myself properly.

    I have attached a file that shows the problem I face - the solution would be a formula that allowed me to drag E3 down, without having to manually type the formula * ( CELL VALUE + 1 ) in each time

    I hope that makes it clearer ;)

    Let me know if it still doesn't make sense! Thanks
     

    Attached Files:

  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I'm sorry, but no, that doesn't make it any more clear than the first time you posted. Actually it makes it more confusing. From your initial post, the formula I gave will do the trick for you. But in the file you posted there are two very different formulas.

    In E3:
    Code:
    =(B2+1)-1
    I'm not even sure what that is supposed to mean. Plus one, then minus one???

    In E4:
    Code:
    =(B$3+1)*(B2+1)-1
    Why are you taking one away from the end of the calculation? Some more description would be most helpful. Remember, the more detailed you get in your description, the closer we get to an actual solution and stop beating around the bush. :)
     
  7. Edoardo

    Edoardo Thread Starter

    Joined:
    Aug 18, 2004
    Messages:
    44
    Ok sorry!! Didn't mean to make things more confusing!! I will break it down:

    Summary
    Think of the data I gave you in Column B as the increasing fatness of my dog each year, expressed as a percentage.

    Step 1
    The raw input data in Column B is expressed in a form like 0.10. The only reason I have the data as 0.10 is because I need it to show as 10%.

    Step 2
    The next step is to calculate the cumulative percentage, to show the overall change in my dog's fatness: I cannot multiply the raw data together as it is (i.e. 0.10 * 0.20) because it would give a very small and wrong answer. Therefore, each percentage must have + 1 added to it to be workable.

    Step 3
    Now, for the first two years, the cumulative, or overall percentage change in my dog's fatness would be 1.1 * 1.2 = 1.32 or 132%. I don't want it to show as this, because it would seem that my dog has balooned. So, I minus 1 from the final answer, giving a much more presentable 0.32 or 32%.

    __________________________

    I hope that explains my example.

    My problem
    In excel, if I try to drag the formula down and let it repeat itself through all the values, it will not do so successfully - it seems to always miss a cell out. Try to drag E3 down and you will find that it will miss (B2 + 1) * from its calculation. This problem occurs every time, and the only solution is to manually type the missing cell in - exhausting if you have a large amount of data!

    Possible solution?
    I think a custom formula that adds one to each value before multiplying would enable me to drag E3 down without any problems.

    Conclusion
    I need a way to multiply raw percentage values together (e.g. 0.10), a bit like PRODUCT does, but very importantly it must add 1 to each value first. As explained above, this is a necessity. I can then minus 1 to the end result by myself.


    :eek: If you have read through all this you are a hero!! Sorry if it is a bit long - hope you understand what I'm trying to do now.
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I think I understand now. I actually don't follow your logic on how you come up with those numbers, but, to get the desired values, enter this formula at the top and copy down. You must enter this formula not just with Enter, but with Ctrl + Shift + Enter, as it's an array formula. Make sure you array-enter the formula in only the first cell, THEN copy down.

    Code:
    =PRODUCT((B2:$B$2)+1)-1
    Edit: btw, when you enter the formula (I should have mentioned this when I posted), Excel will put the squigly parenthesis around your formula. These are NOT manually entered and are perfectly normal. That means you entered it correctly. :) So it should look like this in the formula bar...

    Code:
    {=PRODUCT((B2:$B$2)+1)-1}
    HTH
     
  9. Edoardo

    Edoardo Thread Starter

    Joined:
    Aug 18, 2004
    Messages:
    44
    Zack thank you again, and I'm glad you are clearer (almost) on what I'm trying to do. But are you getting these values from your formula?

    10.00%
    32.00%
    25.40%
    29.16%

    I am only getting 10% using your formula, and I got the array working with squiggly parentheses { }. I have put my workings in the attachment.

    Your formula looks like it makes sense, if
    Code:
    [SIZE="4"]PRODUCT( (B2:$B$2) + 1 ) - 1[/SIZE]
    could, in maths terms, be expanded to
    Code:
    [SIZE="4"](B2 + 1) - 1 ----> (B2 + 1) * (B3 + 1) - 1   ----> (B2 + 1) * (B3 + 1) * .... (B∞ + 1) - 1[/SIZE]
    .....etc. as you dragged the formula down.

    Sorry this keeps going on - you have been really patient - and I'd love it if there was a solution.

    E
     

    Attached Files:

  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I'll go slower for you...
    • Select the top-most cell you want to start with your formulas
    • Paste the formula I provided (w/o squiglies) into the formula bar
    • DO NOT PRESS ENTER TO CONFIRM
    • Press Ctrl + Shift + Enter to confirm
    • --- AT THIS POINT YOU SHOULD ONLY HAVE ONE FORMULA ---
    • Now, with the formula cell selected, hover your mouse over the bottom right (should turn into a bold + sign)
    • Click this + sign and drag down as needed.

    And yes, it gives me those results. There is a big difference between entering a single-cell array function, and entering a function over an array of cells. A BIG difference. Make more sense? :confused:
     
  11. Edoardo

    Edoardo Thread Starter

    Joined:
    Aug 18, 2004
    Messages:
    44
    Thanks Zack - don't worry I get the whole array thing, and I did it as you said the first time round. The parentheses appear after pressing Ctrl+Shift+Enter, but after dragging down, the formula stays the same, and the answer remains 10% for all cells. Maybe its because I've switched to the laptop with OpenOffice Calc - I'll try with Microsoft in the morning, but I shouldn't think that is the problem.

    Could you show me the spreadsheet with the working formula? :D
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    A couple of things. 1) Yes, it makes a difference if you switch programs. I created/tested in Excel 2007, as specified. Not to mention I don't have OpenOffice on this machine. 2) You didn't follow my directions. In the last file you posted I took a look at it and the formula was entered into all of the cells at once. They were a cell-based array, not an array-based formula. It may not sound like much, but it's a HUGE difference. I changed it and have re-uploaded the file with the correct formula. The part where you messed up was in the beginning "Select the top-most cell you want to start with your formulas". You didn't do that, you selected all of the cells you wanted the formula in. Try to slow down a bit, read all of the directions, and then perform each step fully and completely before moving on to the next step. The steps were very specific and, as seen, even a single variance can result in erroneous (or just flat wrong) results.

    Hope this helps
     

    Attached Files:

  13. Edoardo

    Edoardo Thread Starter

    Joined:
    Aug 18, 2004
    Messages:
    44
    Zack! What you have done is genius :D

    It was just Calc that was the problem. Your explanation was great.

    First off, a big THANK YOU!!!(y)

    I have learned so much - I looked up arrays tutorials as well to help me understand what is happening.

    One last question:

    I noticed you choose to write the solution as:
    { =PRODUCT( (B2:$B$2) + 1 ) - 1 }

    and not the one I would naturally write - as no expert:

    { =PRODUCT( ($B$2:B2) + 1 ) - 1 } ?

    I noticed in your version, the $ absolutes go through this process: (B2:$B$2) ----> (B$2:$B3) ----> (B$2:$B4)


    My version does this: ($B$2:B2) ----> ($B$2:B3) ----> ($B$2:B4)

    Q. Looking at the two array versions, do they tell excel to do different things (I know the answers come out the same, but I'm wondering if there is a reason you chose your version over the one that I would naturally have written)?
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    No reason in particular. I actually meant to lock down everything except the last row and think I just didn't go back and change them. I think your 'version' posted is actually easier to understand/read/troubleshoot, and would stick with it.

    Very glad it's working for you now! :) (Don't forget you can mark your thread as Solved by clicking the button up top.)
     
  15. Edoardo

    Edoardo Thread Starter

    Joined:
    Aug 18, 2004
    Messages:
    44
    Will do, thanks for your help! :)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918331

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice