Skip to Content
author's profile photo Former Member
Former Member

apply formula at result level


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,


Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jun 13, 2006 at 09:34 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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.


Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.