Min of the numbers assigned for all the values found in a range

[anshul1719]

Hello,

I'm preparing some document and half-way through it.

I need some help in calculating something like - Min (Numbers Assigned (Search for values in a range)).

Please check the attachment for details.

I need the B6 and B10 to return : Min (Numbers Assigned (Search for values in a range)).

Let me know in case of any clarifications.

Regards,
Anshul

[JBeaucaire]

MIN() values are numeric.

In your sample sheet the raw data we are supposed to evaluate seems to be missing. Can you update?

Sample sheet should include:
1) Raw data to evaluate
2) Manual mockup of the results you want to extract from that sample data.
3) If needed, explanation of how your results were chosen from the sample.

[anshul1719]

Sorry, please check the attachment. I've updated it now.

Regards,
Anshul

[Tony Valko]

Here's the data in your sample file...

Data Range

	A	B	C	D	E
1	Topic	Status
2	A				not applicable
3	B				partly realized
4	C				realized
5	D				not realized
6
7	overall status
8
9	E
10	F
11	G
12	------	------	------	------	------

What are we looking for?

[anshul1719]

Sorry again

Updated it now, please check it.

Regards,
Anshul

[Tony Valko]

Duplicate thread...

https://www.excelforum.com/showthread.php?t=1147699

[anshul1719]

Had requested for deletion of the thread
http://www.excelforum.com/showthread.php?t=1147699

Was posted twice due to internet connectivity issues.

Regards,
Anshul

[Tony Valko]

Updated it now, please check it.

Here's the data in your updated file...

Data Range

	A	B	C	D	E
1	Topic	Status	------	------
2	A	realized			not applicable
3	B	partly realized			partly realized
4	C	not realized			realized
5	D	not applicable			not realized
6	overall status
7	E	realized
8	F	realized
9	G	not applicable
10	overall status
11
12					B6=Min(Numbers assigned (values in B2:B5))
13					B10=Min(Numbers assigned (values in B7:B9))

I have no idea what you're wanting to do.

[anshul1719]

OK, Let me explain in details.

Let's say these are the values assigned -

not realized = 1
partly realized = 2
realized = 3
not applicable = 4

Now, B6 = MIN (Numbers assigned (values in B2:B5)) i.e. MIN (realized, partly realized, not realized, not applicable) = MIN(3,2,1,4) = 1 = not realized.
Hence, B6 should return "not realized".

Similarly, B10 = MIN (Numbers assigned (values in B7:B9)) i.e. MIN (realized, realized, not applicable) = MIN (3,3,4) = 4 = realized.
Hence, B10 should return "realized".

Regards,
Anshul

[Tony Valko]

Try this...

Create the table in E1:F5. It MUST be in the exact order as shown.

Data Range

	A	B	C	D	E	F
1	Topic	Status	------	------	Status	Value
2	A	realized			not applicable	4
3	B	partly realized			not realized	1
4	C	not realized			partly realized	2
5	D	not applicable			realized	3
6	overall status	not realized
7	E	realized
8	F	realized
9	G	not applicable
10	overall status	realized

This array formula** entered in B6:

=INDEX(E2:E5,MATCH(MIN(LOOKUP(B2:B5,E2:F5)),F2:F5,0))

This array formula** entered in B10:

=INDEX(E2:E5,MATCH(MIN(LOOKUP(B7:B9,E2:F5)),F2:F5,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[anshul1719]

It works perfectly.
Thanks! But couldn't understand the logic of creating the table in E1:F5 in the same order.

[Tony Valko]

couldn't understand the logic of creating the table in E1:F5 in the same order.

For it to work properly, the LOOKUP function requires that the items in E2:E5 must be sorted in ascending order.

[anshul1719]

OK great! Thanks a lot

[Tony Valko]

You're welcome!