• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Identify Top 2 and Lowest 2 Value and include in Comment.

R

Raghava@rock

Member
Hi Team, I am looking for some ideas/dynamic formula, to make my comments dynamic based on the sales results from each region. attached is an example.
 

Attachments

  • Dynamic Varaince Summary_Test.xlsx
    11.1 KB · Views: 10
Request you kindly have some patience and wait about 24 hrs before bumping. If you are in a hurry, there are pay forums on the Net which will gladly help at short notice.
This forum is manned by volunteers working for free on their spare time and they are not necessarily waiting behind there screen for your questions.
 
See formula in cell D4 of the attached. Copy down.
82058

The last argument, TopN, currently 2, shows the top and bottom 2. If you change it to, say, 3 you'll get top and bottom 3.
Hidden sheet can be deleted, just shows my scribblings.
 

Attachments

  • Chandoo50173Dynamic Varaince Summary_Test.xlsx
    17.1 KB · Views: 13
Last edited:
@p45cal

Basically, I don't like the OP's conclusion ship comment without any breakdown.

It is very clever of p45cal in using LAMBDA function instead of long traditional method.

Regards
 
pecoflyer said:
Request you kindly have some patience and wait about 24 hrs before bumping. If you are in a hurry, there are pay forums on the Net which will gladly help at short notice.
This forum is manned by volunteers working for free on their spare time and they are not necessarily waiting behind there screen for your questions.
Click to expand...
Apologies for the rush .
 
@p45cal
Great work! I had got as far as extracting the results I needed for a single product but I am pretty sure I wouldn't have produced anything nearly as elegant for the 'narration' element of the work! My sole contribution there was to insert a CHAR(10) between the total sales sentence and the breakdown that follows.

Because I detest direct cell references so vehemently, my copy of your code now reads
= Narrative(salesByRegion, @products, 2),
where the arguments are read into your parameters 'AllRegions' and 'AProduct' respectively.

Then, because I am not fond of the practice of filling down (it always suggests the presence of an unacknowledged array and poses an unanswered question of 'how far?'), I finished with
Code: 
= MAP(products,
      LAMBDA(aProd, Narrative(salesByRegion, aProd, 2))
  )
 
You must log in or register to reply here.
Back
Top