how to? get the name of the lowest sale and in which month ?

**Dj Duck** · 09-23-2014, 04:31 PM

I've been troubling with this issue now for some time ..
Looking and different video's and such but haven't yet found a solution for my problem ..
Now Instead of choosing witch month or name and then get a number from that I instead wanna find find the lowest number in the the array and get the name and Month from those to Create a Top 5 of the lowest/highest names and months

	Jan	Feb	Mar	Apr	May	Jun
Bob	97	59	75	95	86	69
Joe	29	26	71	80	95	86
Ann	42	45	76	78	37	41
Charles	88	37	87	95	82	37
Jill	81	85	46	26	97	28

Now I know that I will need Index to get the name and month but how do I set up the formula to do these things I need ?

Found this video on Youtube that shows how to get the number from choosing the name and month , I just want to do it the other way around (Get the names and months from the number)
https://www.youtube.com/watch?v=kxeSS8n3WNI

PS I've also got the file though there's no calculations in it yet as I don't know how to make them lowest nameandmonth.xlsx

**JieJenn** · 09-23-2014, 04:35 PM

Something like this?

**newdoverman** · 09-23-2014, 05:56 PM

Perhaps this might help you out:

If the array is A6:G11:

The minimum values is in B1:

Formula:

Please Login or Register  to view this content.

To get the name array enter:

Formula:

Please Login or Register  to view this content.

To get the month array enter:

Formula:

Please Login or Register  to view this content.

**Dj Duck** · 09-23-2014, 07:07 PM

JieJenn... You're solution only give a result from one column and not the whole array (b2:G6)

Newdoverman ... You're solution looks good but as far as I know then "Min" only give one result and I want to get 1st, 2nd 3rd
Though it doesn't seem like it can give 2nd and 3rd but I'm not sure how to set that up ... well there is a number 2 and 3 but you're formula doesn't give me those ..

**shg** · 09-23-2014, 07:19 PM

Wouldn't it be easier to just stack the data and sort?

	A	B	C
9	Who	When	What
10	Bob	Jan	97
11	Jill	May	97
12	Bob	Apr	95
13	Charles	Apr	95
14	Joe	May	95
15	Charles	Jan	88
16	Charles	Mar	87
17	Bob	May	86
18	Joe	Jun	86
19	Jill	Feb	85
20	Charles	May	82
21	Jill	Jan	81
22	Joe	Apr	80
23	Ann	Apr	78
24	Ann	Mar	76
25	Bob	Mar	75
26	Joe	Mar	71
27	Bob	Jun	69
28	Bob	Feb	59
29	Jill	Mar	46
30	Ann	Feb	45
31	Ann	Jan	42
32	Ann	Jun	41
33	Charles	Feb	37
34	Ann	May	37
35	Charles	Jun	37
36	Joe	Jan	29
37	Jill	Jun	28
38	Joe	Feb	26
39	Jill	Apr	26

**sktneer** · 09-24-2014, 01:26 AM

Try this.....

	A	B	C	D	E	F	G
1		Jan	Feb	Mar	Apr	May	Jun
2	Bob	97	59	75	95	86	69
3	Joe	29	26	71	80	95	86
4	Ann	42	45	76	78	37	41
5	Charles	88	37	87	95	82	37
6	Jill	81	85	46	26	97	28

		Min	Name	Month
	1	26	Joe	Feb
	2	26	Jill	Apr
	3	28	Jill	Jun

For Min
In B9

Please Login or Register  to view this content.

and copy down.

For Name (Array Formula requires confirmation with Ctrl+Shift+Enter instead of just Enter)
In C9

Please Login or Register  to view this content.

and copy down.

For Month
In D9 (Array Formula)

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

**Dj Duck** · 09-24-2014, 04:59 AM

Thank you SO much sktneer That was exactly what I was after ...

I'm still not that good at those small IF's and Countif's so thanks a lot

**Dj Duck** · 09-24-2014, 07:00 AM

..Actually I found a problem with this ...

if you put 26 in B4 then you will get an incorrect answer .. (Ann, Feb) And (Joe, Jan) Correct should be (Joe, Feb) and (Ann, Jan) What should I do to change this ?

**sktneer** · 09-24-2014, 07:09 AM

Change the formula in D9 to this...
In D9 (Regular Formula)

Please Login or Register  to view this content.

and copy down.

**Dj Duck** · 09-24-2014, 07:36 AM

argh .. man I feel so stupid when you come up with such a simple solution .. Thx a lot for the help ..

Well not so simple ... it seems that if I get same name in C9-11 then the match will give me same month and not the next one

**sktneer** · 09-24-2014, 07:42 AM

You're welcome.

If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

**Dj Duck** · 09-24-2014, 07:46 AM

problem arises if ex.. you change E3 and give it 26 as well .. then I will get 1(Joe, Feb) 2(Joe,Feb) 3(Jill,Apr)
Should be !(Joe,Feb) 2(Joe,Apr) and 3(Jill,Apr)

**sktneer** · 09-24-2014, 07:50 AM

OK. In this case change the formula in D9 to this Array Formula which will require confirmation with Ctrl+Shift+Enter.

In D9

Please Login or Register  to view this content.

and copy down.

**sktneer** · 09-24-2014, 07:53 AM

No I think this will work this this scenario but not with the earliest one. Let me see.

**sktneer** · 09-24-2014, 08:02 AM

Try this Array Formula in D9 and test it if you get correct output for all the scenarios.

In D9

Please Login or Register  to view this content.

and copy down.

**romperstomper** · 09-24-2014, 08:12 AM

I'm with shg on this.

**Dj Duck** · 09-24-2014, 08:21 AM

Yea THAT seem to have done the trick .. now before I make this Solved .. with these changes .. you sure there's no need for changes in the Name section ?

Must say .. that with all that Countif, Sumproduct and those things I would never had solved this myself ... Will try it out with diff values and such for an hour to see if any problems arise before I mark it Solved ..

how to? get the name of the lowest sale and in which month ?

LinkBack

Thread Tools

Rate This Thread

Display

how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Re: how to? get the name of the lowest sale and in which month ?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] define exact number of products sale from multiple sale and returned sale orders

Calc Final Sale Price Including % of Sale?

return lowest monthly no and month name

[SOLVED] calculation sales of particular month among months of sale

If function with date; if sale is Oct place sale $ amount here

Bookmarks

Bookmarks

Posting Permissions