Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
Hi
Is it possible to make it work in excel in this situation: I've got 3 columns: A,B,C. I want to find the smallest number in col C, but including rows with the same value in col B. The answer would be from col A with the same row as found value in C. Example: A B C A1 J 8 A2 K 4 A3 J 6 A4 P 1 I'm looking for lowest number in col C but including only rows w "J" in col B. So, the answer should be "A3" in col A. Any ideas how to make it work? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
=MIN(IF($A$2:$A$5="j",$B$2:$B$5))
array-enter this formula i.e. with CTRL+SHIFT+ENTER instead of using ENTER only On 4 Lis, 11:18, "soonic" wrote: Hi Is it possible to make it work in excel in this situation: I've got 3 columns: A,B,C. I want to find the smallest number in col C, but including rows with the same value in col B. The answer would be from col A with the same row as found value in C. Example: A * *B * *C A1 *J * * 8 A2 *K * *4 A3 *J * * 6 A4 *P * * 1 I'm looking for lowest number in col C but including only rows w "J" in col B. So, the answer should be "A3" in col A. Any ideas how to make it work? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
Uzytkownik "Jarek Kujawa" napisal w wiadomosci
... =MIN(IF($A$2:$A$5="j",$B$2:$B$5)) array-enter this formula i.e. with CTRL+SHIFT+ENTER instead of using ENTER only thank you, but this is the first part. In final answer should be a value from col A as the same row of found number in C. It doesn't work with my example. Col A have values A1, A2, A3, A4. The answer should be "A3". |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
On 4 Nov, 11:18, "soonic" wrote:
Hi Is it possible to make it work in excel in this situation: I've got 3 columns: A,B,C. I want to find the smallest number in col C, but including rows with the same value in col B. The answer would be from col A with the same row as found value in C. Example: A * *B * *C A1 *J * * 8 A2 *K * *4 A3 *J * * 6 A4 *P * * 1 I'm looking for lowest number in col C but including only rows w "J" in col B. So, the answer should be "A3" in col A. Try: =INDEX($A$2:$A$5,MATCH(MIN(IF(($B$2:$B$5)="J",($C$ 2:$C$5))),($C$2:$C$5) *(($B$2:$B$5)="J"),0)) N.B.: Matrix formula (confirm with CTRL+SHIFT+ENTER) Bye! Scossa |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
I will assume your data goes down to row 20
In F1 I have typed the letter J - the letter you are looking for I will show you how I developed the formula to get A3 In F2 I used =MIN(IF(B1:B20=F1,C1:C20)) and entered this as an array formula using CTRL+SHIFT+ENTER (not just Enter) This correctly returned the value 6 Now I want to know where this occurred. I used this array formula =MATCH(MIN(IF(B1:B20=F1,C1:C20)),C1:C20,0) which returned 3 since the 6 is in the third item in C1:C20 Finally I want to know what is in column A in this position, this I can find with array formula =INDEX(A1:A20,MATCH(MIN(IF(B1:B20=F1,C1:C20)),C1:C 20,0)) This returned A3 as we hoped it would best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "soonic" wrote in message ... Hi Is it possible to make it work in excel in this situation: I've got 3 columns: A,B,C. I want to find the smallest number in col C, but including rows with the same value in col B. The answer would be from col A with the same row as found value in C. Example: A B C A1 J 8 A2 K 4 A3 J 6 A4 P 1 I'm looking for lowest number in col C but including only rows w "J" in col B. So, the answer should be "A3" in col A. Any ideas how to make it work? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
On 4 Nov, 14:56, "Bernard Liengme" wrote:
with array formula =INDEX(A1:A20,MATCH(MIN(IF(B1:B20=F1,C1:C20)),C1:C 20,0)) This returned A3 as we hoped it would in this examples (with the min (6) also for K): A1 J 8 A2 K 6 A3 J 6 A4 P 5 don't works. My solution: =INDEX($A$2:$A$5,MATCH(MIN(IF(($B$2:$B$5)="J",($C$ 2:$C$5))),($C$2:$C$5) *(($B$2:$B$5)="J"),0)) works fine becouse target are "fltered" for "J". Bye! Scossa |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
My solution:
=INDEX($A$2:$A$5,MATCH(MIN(IF(($B$2:$B$5)="J",($C$ 2:$C$5))),($C$2:$C$5) *(($B$2:$B$5)="J"),0)) works fine becouse target are "fltered" for "J". this is good point! thank you for help Scossa it works great now. Thank you also Bernard for explanation. I've got another problem in my real excel data. I'm using outline in every row and this array formula is being used inside it, so pressing ctrl,shift and enter gives me an error. Going back to my example add a row between rows C1 nad C5 and try to put this array formula in C3. You will get an error after pressing ctrl+shift+enter. How to exclude the whole row which contains this array formula from its range? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
On 4 Nov, 21:32, "soonic" wrote:
this is good point! thank you for help Scossa it works great now. Thank you also Bernard for explanation. Thank you for backfeed. I've got another problem in my real excel data. I'm using outline in every row and this array formula is being used inside it, so pressing ctrl,shift and enter gives me an error. Going back to my example add a row between rows C1 nad C5 and try to put this array formula in C3. You will get an error after pressing ctrl+shift+enter. How to exclude the whole row which contains this array formula from its range? Try this: fill outline cells with a "dummy" value (p.e.: "Z1" in A, "Z" in B, 0 in C) foreground color same as backgroundcolor. Apologize for my bad english. Bye! Scossa |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
On 4 Nov, 22:02, Scossa wrote:
On 4 Nov, 21:32, "soonic" wrote: this is good point! thank you for help Scossa it works great now. Thank you also Bernard for explanation. Thank you for backfeed. opps... feedback sorry. Bye! Scossa |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
Try this: fill outline cells with a "dummy" value (p.e.: "Z1" in A, "Z" in B, 0 in C) foreground color same as backgroundcolor. I'm sorry but I don't get it now. This array formula is in a cell of C column like: A B C a1 K 6 a2 J 8 x xv array forumula a4 J 6 when accepting it by pressing ctrl+shift+enter I get an error (or information something with "recall serial inside formula") and then it shows 0. The best it would be to exclude the outline from the range of array formula, I don't now how to do it.(the easiest way is to change the location of the written formula but it would mess up in my spreadsheet) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding a value with parameters
My solution: =INDEX($A$2:$A$5,MATCH(MIN(IF(($B$2:$B$5)="J",($C$ 2:$C$5))),($C$2:$C$5) *(($B$2:$B$5)="J"),0)) can you modify this formula so it would show all the same matches? Like in the example below A B C a1 n 4 a2 J 6 a3 J 7 a4 J 6 ..... the answer would be a2 and a4. Is it possible to do that without VB? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
parameters | Excel Discussion (Misc queries) | |||
Too few parameters | Excel Discussion (Misc queries) | |||
SQL & Like Parameters | Excel Discussion (Misc queries) | |||
Too few parameters | Excel Discussion (Misc queries) | |||
too few parameters | Excel Discussion (Misc queries) |