apply formula at result level

[Former Member]

Hi,

I need to build a report to get a KF of inventory turnover, the formular used is:

<i>turnover =total consumption / average stock</i>

while we use the formular below to get the

<b>average stcok =(begin of stock + n stock at month's end)/(n+1)</b>

what I need is to based on user's date input(let's say 09/2005 to 11/2005 for example),

1,get the "total consumption" for Oct and Nov,

2,and also get

"average stock" = (Stock at Sep + Stock at Oct + Stock at Nov)/(2+1)

3, get the turnover = "total consumption" /"average stock"

let's say KF "Total cnsumption" for 10/2005 is 10, for 11/2005 is 20. so <b>"Total cnsumption" for Oct and NOv</b> will be 10+20=30;

and if KF "Stock at Sep" = 12, "Stock at Oct" is 24,

"Stock at Nov" = 30

then <b>"average stck"= (122430)/(2+1) = 22</b>so the "turnover" will be

<b>"Total cnsumption"/"average stck"= 30/22= 1.36</b>

I am not sure how the report lay out will be, but as long as i can get the turnover based on teh user input's month during, that would be good enough. so probably the report will look like:

"Month" "Totalcnsump" "Stck" "averageStck" "TurnOver"

09/2005 12

10/2005 10 24

11/2005 20 30

<b>result: 30 66 22 1.36</b>

Does any one have any idea how to make it done???

Your input is very much apprecieated,

ping

[Former Member]

dose any one knwo how to write a macro at teh workbbok level, so that i can get the "turnover" by use the result value for "total consumption", divided by the the result value of "average stck".

in my example,

"Month" "Totalcnsump" "Stck" "averageStck" "TurnOver"

09/2005 12

10/2005 10 24

11/2005 20 30

result: 30 66 22 1.36

it should be

"turnover" = 30/22 =1.36

[Former Member]

Have you recieved an answer to your question. I am having the same issue with my clients requirement. If you have a solution, please send me an email to rodg33@hotmail.com

Thanks

[Former Member]

Can you send the Excel File to my id so that I can write the macro and send to you.My id is krishnamoorthy.gopalakrishnan@wipro.com.Put a cc to radigopi@yahoo.com

Regs

Gopi.

[Former Member]

My Id is wuping1@yahoo.com

so could you please send the macro to my yahoo account.

Thanks,

Ping

[Former Member]

Dear Ping,

This may not suit your requirements perfectly.

But if your report layout is "fixed," how about adding a formula in the XLS workbook for example at column "G" such as:

= IF(cell = "Result", apply the turnover formula cell1/cell2, else 0).

As I have said, this is not the best way to do it.

How about defining 2 structures in your query and try the options using cells definition in the query designer. You may wish to try and have a look at it for other possible solutions.

Thanks a lot.

--