Creation of a UDF function for excel

[meowee]

I'm quite new to this.

I'm trying to create a formulae that does this.

=mul(3)

then it will automatic display 3 in the cell and -6 in the column directly below.

please help

[meowee]

I've done this, but it's not working.

Please Login or Register  to view this content.

[dredwolf]

NO formula can affect a cell other than the one it is in, so what you really need is a conditional formula that checks the cell above it to see if it contains 3, then it displays 6, also.....umm...whats mul(3)...i cannot find that function...

[dredwolf]

Aaah...an UDF...the rules still apply, will only return a value, cannot change another cell..make it a Proc and it MIGHT work...

-Edit-
Also, A procedure will not return a value

[:) Sixthsense :)]

Why not 3 in A1 cell and and in A2 =(A1*-2)?

Please explain your requirement clearly for our better understanding.

[meowee]

Because I need a row of numbers A3,A4,A5... whereby A3 = A2*(-0.5), A4=A3*2.1, so on

So I figure if I can just do =mul(3), it will work out what I need.
and since it repeats fairly often I will save a lot of time.

[:) Sixthsense :)]

In which basis the counter (-0.5) and 2.1 is increasing? could you please explain the logic?

[meowee]

basically =mul(5)

i want to yield the result, and since it is recurring a lot in my spreadsheet, i can do mul(3), mul(4),

5
(10)
5
10

[:) Sixthsense :)]

Thanks for the reply please continue the next subsequent series....

[meowee]

I just need help to create this one UDF. The next subsequent series is not required.

[meowee]

Can anyone help in this?

Essentially, I like the use a formulae like =mul(5) to get the result

5
(10)
5

or mul(-3) to get
(3)
6
(3)

Since it is recurring, it will help me save a lot of time.

[MrShorty]

I, too, do not understand your reasoning for putting this into a UDF, but I won't try to dissuade you. A few things I've learned:

1) A UDF can return multiple values if it is programmed as an array function. One of the first things you will need to learn is how to enter array functions into Excel. I would suggest a search of the Excel help files for "entering array formulas" and the help files for array functions like the transpose(), minverse(), linest() functions. If you are unfamiliar with array functions, this should help you understand the mechanics (ctrl+shift+enter vs enter) of entering an array function.
2) I have never had success declaring a function as an array function. I've had good success declaring the function as a variant type, dimensioning an array to temporarily store the result while the function is running, then assigning the function to return that array. This looks something like this:

Please Login or Register  to view this content.

[ChemistB]

Putting in my 2¢,
As someone mentioned earlier, a UDF (or any other Excel Equation) can only affect the cell it is in. You need to use an event triggered macro to affect values in other cells. If I could figure out your logic, this would be fairly simple but I'm having trouble. An example worksheet might help a lot here, showing several examples (go advanced> Manage attachments).

you state

Because I need a row of numbers A3,A4,A5..

do you need a column of numbers or a row of numbers? A2,A3, A4 is a column. I was thinking that you always wanted to multiply by a specific number down the column but in your example, you use -0.5, then 2.1.

[meowee]

It's basically =mul(5)that is entered into a cell or a block of 3 cells to produce the output.

cell A1 5
cell A2 (10)
cell A3 5


Maybe it is not possible.

[ChemistB]

Why is A3 = 5? Shouldn't it be -2* A2 or 20? I think this would be possible once I figure out the logic.

[:) Sixthsense :)]

May be this...

Assuming Cell A1 is your input cell

A1 cell
5

In A2 cell
=IF(ROW()=2,$A$1*(-2),$A$1)
Drag it to A3 also.

I have posted this yesterday itself but some of my posts are disappearing I don't know the reason...