Excel Sales Task Questions

[BigErnKingpin]

Hiya,

I am trying to find the best ways to demonstrate the most efficient means of displaying sales data in various ways including the best type of graphs.

I could do it using pivot tables and graphs but was wondering if this is the best way or can anyone demonstrate a better way?

I have attached a redacted file with some questions for reference.

1. Which Account has the most overall wins and what is the % share of total wins?
2. Which Account has the most open sales and what is the % share of total open?
3. Name the accounts that are 10th to 20th on total wins and total open
4. Which products have the most wins and which have the lowest?
5. Where would you focus a sales campaign outside product C?

Thanks

[Glenn Kennedy]

This looks like homework. I appreciate that you may not know the correct answer, but it would be incorrect of me to deprive you of your education by doing it for you.

So, what have YOU attempted? What functions did YOU try? What hapened? How did they fail?

[BigErnKingpin]

This is something I just downloaded from the interwebs to try out as the layout is quite different from my usual spreadsheets.

I have attached another version to this reply.

Ta

[Glenn Kennedy]

You have ducked the issue. Is this homework?

[BigErnKingpin]

This is me practicing. I have become too reliant on pivot tables and want to ensure I am a. Using the correct formulas and b. I am getting the right results.

I have downloaded a load of similar example spreadsheets from a few websites and I'm working my way through them.

I have attached a "worked" version to last reply but I want to know if there are better methods to use and that the results I am getting are correct. No point in me thinking I have done them if they are totally wrong lol

So for example have I used the RANK function correctly and is it bringing back the correct results. It is easy to do rank results on a pivot but I'm far too reliant on pivot tables.

[Glenn Kennedy]

OK!! You should always make this sort of thing clear. Most of us ARE happy to help, but are reluctant KNOWINGLY to let users cheat at homework.

[BigErnKingpin]

Mate, I am 40 and my days of homework are far far gone lol

[BigErnKingpin]

What I'd actually like is for it to actually look good and be set out right cos again pivots kind of do that for you too

[Glenn Kennedy]

Since I was learning myself, I cut the sample down to a REAL sample - a few columns & rows.

Take a look and feel free to come back with any Qs.

Lots of array formulae like this:

=MAX(MMULT($B$3:$G$7,TRANSPOSE(--($B$2:$G$2="Open"))))

and this:

=MAX((--TRANSPOSE($B$2:$G$2="Open")*MMULT((--TRANSPOSE($B$3:$G$7)),(ROW($B$3:$B$7)^0))))

and this

=INDEX($B$1:$G$1,MATCH(L23,IF(--TRANSPOSE($B$2:$G$2="Open")=0,10^10,--TRANSPOSE($B$2:$G$2="Open")*MMULT((--TRANSPOSE($B$3:$G$7)),(ROW($B$3:$B$7)^0))),0))

[BigErnKingpin]

This is exactly why I ask for advice. The more basic data set you have is a hell of a lot easier to follow and understand than the one I was trying to work from.

So the MMULT function is something I have never even heard of nevermind used. Is that exclusively used when trying to get a result between 2 different array calculations?

Thanks a lot. I am going to go have a proper look into these and come back with any questions.

Again thanks for the help because it really is appreciated as these are not something that would be easily created by someone even if I spent hours trawling through various "excel guru" websites.

[Glenn Kennedy]

Its used when multiplying two arrays. Feel free to ask Qs.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[BigErnKingpin]

I had already marked it as solved and added reputation but I have a further question. If i had a larger list and wanted to pinpoint the exact rank of the accounts by wins and by opens is the formula I had on the worked sheet I attached above sufficient and correct or would you use something else?

Cheers mate

[Glenn Kennedy]

??? The exact rank of the one with the most wins??? Isn't that 1, by definition??

Im away now for the day.

[BigErnKingpin]

Apologies, I totally mistyped that. I meant the ranking of the accounts by wins and opens so I can pinpoint a particular accounts ranking for each.

Hopefully you can answer at some point.

Thanks again for your help and have a great evening.