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

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

Not open for further replies.

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.

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

Joined:
Aug 18, 2004
Messages:
44
Thanks

To summarise, the formula I am trying to create is

Product(CELL VALUE + 1)

Is this even possible using macro?

4. 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))`

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

File size:
7.5 KB
Views:
31
6. 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.

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.

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

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

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&#8734; + 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

File size:
8.5 KB
Views:
39
10. Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
I'll go slower for you...
• 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?

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?

12. 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:

• Edoardo's problem Part 3.xls
File size:
20.5 KB
Views:
28

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

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

First off, a big THANK YOU!!!

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

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.)

Joined:
Aug 18, 2004
Messages:
44
Will do, thanks for your help!

As Seen On