syntax for the macro - request suggestions...

[Former Member]

Dear All, Good morning..

I request your help in craking the below issue..

<i><b>Description:</b></i>

Calculation of a KF based on the following formula:

<b>Dlr Inv(M01/2007)= Max( ( sp.plan(m01/2007)+Dlr Inv(M12/2006) ) ; Threshold Dlr Inv(M01/2007) )- Un constrained sales( M01/2007)</b>

If you observe in the above calculation, for calculating current month Dlr Inv, we are using previous months Dlr Inv..OK.

<i><b>Requiremet:</b></i> Calculation of the above , plus, the above calculation may produce a negative number...So, when there is negative number generated in the curent month calculation, i should make it to zero, display zero and carry that zero for next months calculation...

I wrote a macro, which is doing above calculation perfectly(as it is simple )...except carrying zero to next month's calculation...As long as the above calculation is generating positive value, there is no issue at all..

<i><b>Issue:</b></i> In some cases , the above formula results a negative number, in that case, i am able to make it zero, and able to carry that zero for next calculation, if the negative is generated for the first column..problem is , if there is any negative generated in between,let us say, for June/2006, then its making it zero, but that zero is displayed for the subsequent months from there onwards, irrespective of the result is positive or negative..If i chage any number at that period for any KF, and execute macro, then it is working..Here i used auxiliary row...

I have written 3 macros with diff logic...If you want i can post them again ....

I would like to know the following things regarding macro syntax..

<b>1)Can we go for a condition after a result in the same step...?

</b>

<b>Example:

Step:(M01/2007-M12/2007)(12 iterations)

Row 1= Row2 -Row 3

IF

Row 1 < 0

Row 1 = 0

ENDIF</b>

If i execute IF condition in another step,It won't serve my purpose because, it comes to second step after completing all iterations in first step...I want , <b>IF</b> condtion to be checked after the first iteration..

In my case, macro successfully activated but no result for that condition..It is executed only up to result for the first time when i tested in Utilities--->test Macros..in the macro builder..if i execute it for the second time, then it gives result..Surprised..!!!

2) <b>Is it possible to write syntax in the following way..</b>

IF

(condition)

( Row 1 + Row 2) < 0

ResulT : Row 3=0

In my case,

It is not accepting +, - .....

Kindly suggest...

I am confident that i crack this with your mighty help..

Thanks for reading..

regards,

umamahesh

Message was edited by: Uma Mahesh

Uma Maheswar kumar

Message was edited by:

Uma Maheswar kumar

[Former Member]

Hi Uma,

I suggest following. When you are taking value of last months for current month's calculation do not take it directly from the pervious cell reference. Do the calculation for pervious month's value again in the current month and if that value is -ve then take zero. I am taking a simple formula to explain my point. Suppose the formula is

Vi = Vi-1 + Xi. Then calculate the value of Vi-1 in the current month using aux KF

say

Ai-1 = Vi-2 + Xi-1.

So IF Ai-1 < 0. then Ai-1 = 0. Endif.

And

Vi = Ai-1 + Xi

Clear value of Ai-1.

Hope this helps.

Regards,

Vinayak Oak

[Former Member]

HI Vinayak,

Thanks very much for responding..

I appreciate your reply and time spent on this...

Your solution goes to indefinate internal loop..

In your solution, Vi-2 is the previuos years cell ref: which again needs calculation...

Your solution works good if i have to use current motns calculation of one KF in another...

say

A= B+C;(current month)

D=AE( current momth)...then instead of AE, better we use, D=(BC) E...Actually i am not facing any problem in taking the previous months updated value...i am facing problem with IF loop in case of negative...

I will try your option and come back to you...

[Former Member]

Hi Uma,

It will not go into infinite loop as you are using time stamp for the KF for Vi-2. So you need to use the KF for Vi-2 with past date.

Hope this helps.

Regards,

Vinayak Oak

[Former Member]

Hi Uma,

It will not go into infinite loop as you are using time stamp for the KF for Vi-2. So you need to use the KF for Vi-2 with past date.

Hope this helps.

Regards,

Vinayak Oak

[Former Member]

Hi Vinayak,

I solved the issue....

Here as the main requirement is not to take negatives, i have used anothe MAX() function for the whole formula and compared it to zero..

Dlr Inv(M01/2007)= MAX(Max( ( sp.plan(m01/2007)+Dlr Inv(M12/2006) ) ; Threshold Dlr Inv(M01/2007) )- Un constrained sales( M01/2007) ; 0 )

This will not generate negative...

Thanks for your reply and time spent on this..

I would like to award points to you, but unfortunately here are no radio buttons for points..some problem with GUI..

If u give me ur personal mail id, i will send u my greetings...

regards,

Uma Mahesh

[Former Member]

id is oakvinayak@yahoo.com.

Thanks

Vinayak Oak