| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there any funcion like a @maxif or @minif?
I need to calculate the max and the min of a certain column (say B) as long
as the name in Col A matches a certain value. Basically it would have to do the equivalent to @sumif. I can't find any function that does that... Is there any? In alternative, can I use subtotals but in such a way that the results are in a different sheet, not in the same sheet as the data? Thanks in advance for your help! Regards, Just Anna |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there any funcion like a @maxif or @minif?
You could use an if statement like:
=IF(A1="Hello",MAX(B:B),"SkipIt") Just Tom |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there any funcion like a @maxif or @minif?
It a matter of 'rolling your own'; you need to modify MAX to do what you
want This worked for me: =MAX(IF(A1:A15="a",B1:B15,"")) In A col I had letters like 'a', 'b', 'c' In B col I have numbers The formula found the largest value in B col when the corresponding A col entry was 'a' NOTE: this is an array function and must be entered with CTRL+SHIFT+ENTER, not with just ENTER Excel will enclose your formula within { } in the Formula Bar best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Just Anna" wrote in message ... I need to calculate the max and the min of a certain column (say B) as long as the name in Col A matches a certain value. Basically it would have to do the equivalent to @sumif. I can't find any function that does that... Is there any? In alternative, can I use subtotals but in such a way that the results are in a different sheet, not in the same sheet as the data? Thanks in advance for your help! Regards, Just Anna |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there any funcion like a @maxif or @minif?
Thanks for your quick answer, Just Tom, but I'm afraid it didn't work. May be
because instead of "Hello" I have a cell (it's a huge list of cels for which I need to calculate max and mins), not a value... Thanks anyway... R, Just Anna "TomPl" wrote: You could use an if statement like: =IF(A1="Hello",MAX(B:B),"SkipIt") Just Tom |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there any funcion like a @maxif or @minif?
Hi Bernard,
I'm afraid it keeps returning #VALUE. I had tried this before posting this question, and have just tried again. Sorry, still no luck... Thanks anyway, Just Anna "Bernard Liengme" wrote: It a matter of 'rolling your own'; you need to modify MAX to do what you want This worked for me: =MAX(IF(A1:A15="a",B1:B15,"")) In A col I had letters like 'a', 'b', 'c' In B col I have numbers The formula found the largest value in B col when the corresponding A col entry was 'a' NOTE: this is an array function and must be entered with CTRL+SHIFT+ENTER, not with just ENTER Excel will enclose your formula within { } in the Formula Bar best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Just Anna" wrote in message ... I need to calculate the max and the min of a certain column (say B) as long as the name in Col A matches a certain value. Basically it would have to do the equivalent to @sumif. I can't find any function that does that... Is there any? In alternative, can I use subtotals but in such a way that the results are in a different sheet, not in the same sheet as the data? Thanks in advance for your help! Regards, Just Anna |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there any funcion like a @maxif or @minif?
Post the exact formula that returns the error
-- Regards, Peo Sjoblom "Just Anna" wrote in message ... Hi Bernard, I'm afraid it keeps returning #VALUE. I had tried this before posting this question, and have just tried again. Sorry, still no luck... Thanks anyway, Just Anna "Bernard Liengme" wrote: It a matter of 'rolling your own'; you need to modify MAX to do what you want This worked for me: =MAX(IF(A1:A15="a",B1:B15,"")) In A col I had letters like 'a', 'b', 'c' In B col I have numbers The formula found the largest value in B col when the corresponding A col entry was 'a' NOTE: this is an array function and must be entered with CTRL+SHIFT+ENTER, not with just ENTER Excel will enclose your formula within { } in the Formula Bar best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Just Anna" wrote in message ... I need to calculate the max and the min of a certain column (say B) as long as the name in Col A matches a certain value. Basically it would have to do the equivalent to @sumif. I can't find any function that does that... Is there any? In alternative, can I use subtotals but in such a way that the results are in a different sheet, not in the same sheet as the data? Thanks in advance for your help! Regards, Just Anna |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there any funcion like a @maxif or @minif?
Sorry... I've just realised I had an extra argument in the if function...IT
DOES WORK!!!! Thank you Just Tom, Bernard and Peo! :-) Regards, Just Anna "Peo Sjoblom" wrote: Post the exact formula that returns the error -- Regards, Peo Sjoblom "Just Anna" wrote in message ... Hi Bernard, I'm afraid it keeps returning #VALUE. I had tried this before posting this question, and have just tried again. Sorry, still no luck... Thanks anyway, Just Anna "Bernard Liengme" wrote: It a matter of 'rolling your own'; you need to modify MAX to do what you want This worked for me: =MAX(IF(A1:A15="a",B1:B15,"")) In A col I had letters like 'a', 'b', 'c' In B col I have numbers The formula found the largest value in B col when the corresponding A col entry was 'a' NOTE: this is an array function and must be entered with CTRL+SHIFT+ENTER, not with just ENTER Excel will enclose your formula within { } in the Formula Bar best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Just Anna" wrote in message ... I need to calculate the max and the min of a certain column (say B) as long as the name in Col A matches a certain value. Basically it would have to do the equivalent to @sumif. I can't find any function that does that... Is there any? In alternative, can I use subtotals but in such a way that the results are in a different sheet, not in the same sheet as the data? Thanks in advance for your help! Regards, Just Anna |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there any funcion like a @maxif or @minif?
Hi,
And going back to your original question: =MIN(IF(A1:A15="a",B1:B15,"")) FYI, Excel added a new function in 2007 called AVERAGEIF but unfortunately not the MINIF and MAXIF. They also added SUMIFS, AVERAGEIFS, and COUNTIFS, note the "S". But probably the most important new function is IFERROR. -- Thanks, Shane Devenshire "Just Anna" wrote: Sorry... I've just realised I had an extra argument in the if function...IT DOES WORK!!!! Thank you Just Tom, Bernard and Peo! :-) Regards, Just Anna "Peo Sjoblom" wrote: Post the exact formula that returns the error -- Regards, Peo Sjoblom "Just Anna" wrote in message ... Hi Bernard, I'm afraid it keeps returning #VALUE. I had tried this before posting this question, and have just tried again. Sorry, still no luck... Thanks anyway, Just Anna "Bernard Liengme" wrote: It a matter of 'rolling your own'; you need to modify MAX to do what you want This worked for me: =MAX(IF(A1:A15="a",B1:B15,"")) In A col I had letters like 'a', 'b', 'c' In B col I have numbers The formula found the largest value in B col when the corresponding A col entry was 'a' NOTE: this is an array function and must be entered with CTRL+SHIFT+ENTER, not with just ENTER Excel will enclose your formula within { } in the Formula Bar best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Just Anna" wrote in message ... I need to calculate the max and the min of a certain column (say B) as long as the name in Col A matches a certain value. Basically it would have to do the equivalent to @sumif. I can't find any function that does that... Is there any? In alternative, can I use subtotals but in such a way that the results are in a different sheet, not in the same sheet as the data? Thanks in advance for your help! Regards, Just Anna |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| funcion de distribucion empirica | Excel Worksheet Functions | |||
| minif | Excel Worksheet Functions | |||
| Minif / Maxif ? | Excel Worksheet Functions | |||
| MINIF /MAXIF Or something similar | Excel Discussion (Misc queries) | |||
| how do see add-in a funcion belongs to | Excel Discussion (Misc queries) | |||
Linear Mode
