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

Highest & lowest sales value (by Region,Salesperson & Product)

K

Khurrum

New Member
Hello,

PFA excel file. Looking for a single cell formula (possible an array formula) whereby I could determine highest and single sales value, to guage sales performance by region, salesperson and product. The problem is already solved by a Pivot table, but I want it to be dynamic in the event of change in the variables.
upload_2014-12-8_1-37-23.png
 

Attachments

  • Array FormulaQ2.xlsx
    1,004.9 KB · Views: 16
Thanks Mr. Somendra

I appreciate your work and time.

But I think you used "results" as variables, and therefore, results are not achieved, but atleast it moved to one of the required steps.

I need to know how to get single highest and lowest sales value under 1 instance or it is not possible.

=Vlookup() can be use to determine the region name once we get the value.
 
@Khurrum

The formula provided based on your file. You did not mention where you want result i.e. in which cell, what will be inputs, kindly state all such information to give you a correct result.

Regards,
 
Sorry Somendra

You are free to provide the results, anywhere, but use the format of the expected result.

Input Sales Data is there
Expected Result table is there
Pivot table is there to check my requirement.

You can check this file again
 
Wll I think your expected result table in in the range O2:U7 on sales sheet? If so than it doesn't match with Pivot format, you need to change the output format.

Regards,
 
You are right Somendra.

But reason is this I have more than 100000 lines of data of about 26 salespersons, with over 15 products how is it possible to show (26 x 4) 104 lines for each sales person, and 6 other similar variables for other analysis.

If I change the format it is like a another database, which is not hold good when you are evaluating summaries.

Is there any way if you match 4 regions (as name range) for each record to get maximum and minimum value simultaneously.

Instead of showing the region's name in front of each record. Once the criteria is met it prints dynamically the region's name in front of the min and max respective record.

I hope you got that right and reasonable.

Regards,
 
Mr. Somendra & all members

Is it possible with IF(AND).... statement or any other method matching new range name comprise of (North,East,West,South) as RegionCriteria matching each record under the existing range name as Region or =MMULT(), =CHOOSE(), ROW() or COLUMN() functions or any method you think can work.

upload_2014-12-9_11-50-59.png




Regards,
 

Attachments

  • Array FormulaQ2-v2.xlsx
    894.8 KB · Views: 1
@Khurrum

The problem is not the selection of function but to deliver a single cell solution. If you consider all regions with one names for one product, it will deliver may be 4 result, now you can display only one value in a cell, rest values which are not shown will not serve any purpose.

Regards,
 
@Khurrum

Just one more point:

Is the expected result that you showed in your sample file is the required result if not than can you post the complete result for the sample data.

Regards,
 
See the file, which is producing the result as you posted. But note if the query is from some quiz and it's a special case where MAX and MIN are unique this formula will work, otherwise if there are duplicates than this will fail and we may need to change the formula or approach towards problem.

Regards,
 

Attachments

  • Array FormulaQ2-v2.xlsx
    895.5 KB · Views: 3
Mr. Somendra,

BY CONDITIONAL FORMATTING WE WOULD HIDE UNWANTED CELLS RIGHT, BUT WE NEED A FORMULA AS YOU RIGHTLY SAID, GIVE US, 'SINGLE CELL SOLUTION'. IF THIS EVEN DONE, IT WILL ACHIEVE ITS GOAL.

In essence we need to do 3 things

- evaluate all possible single sales values highest or lowest in all cells by single cell formula, if it is high/low among 6 products in 4 regions and among 5 salesperson
- then hide unwanted cells by conditional formatting if single cell formula does not allow it
- show/evaluate the region name in front of each sales person

Do you think it can be challenging question if possible and need forum input, as well? Whats your take?

Simplest way (without array formula and single cell solution)
Can we rank using RANK() function each sale value through a helper column and show the single highest sale using =LARGE() function value and lowest sale value using =SMALL() and leave single cell solution and get the answers through VLOOKUP().

Regards,
 
Thanks a lot Mr. Sumendra.

I find your formula work fine but again you pointed it right in case of duplicates it will change the whole scenario all together, may be "total sales value" achieved by a sales person in all regions may be the final argument, if it not be further duplicated afterwards. Let me work on it, further what is the best argument to be used.

In the mean time send me your email we will in touch for more, if you agree.

Best regards,

Khurrum
 
You must log in or register to reply here.
Back
Top