|
Calculating Maximums
I have 2 columns in a spreadsheet. Column 1 has values of (A, B or C),
Column 2 has numeric values. In one cell I want to calculate the maximum value for all of rows where column 1 = "A". Example: column 1 column2 A 10 A Max = 15 A 5 B Max = 9.25 B 3 C Max = 3 C 2.5 B 9.25 C 3 A 15 How can I calculate this? |
Calculating Maximums
=Max((A1:A50="A")*B1:B50)
entered with Ctrl+Shift+Enter rather than just enter since this is an array formula. or assume the A is in C1 =Max(($A$1:$A$50=C1)*$B$1:$B$50) -- Regards, Tom Ogilvy L Buchy wrote in message ... I have 2 columns in a spreadsheet. Column 1 has values of (A, B or C), Column 2 has numeric values. In one cell I want to calculate the maximum value for all of rows where column 1 = "A". Example: column 1 column2 A 10 A Max = 15 A 5 B Max = 9.25 B 3 C Max = 3 C 2.5 B 9.25 C 3 A 15 How can I calculate this? |
Calculating Maximums
Might want to take another look at that formula.
Regards, Tom Ogilvy Dave Peterson wrote in message ... You could use a formula like: =IF(A1:A7="A",MAX(B1:B7)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Another way when your list of values in column A gets larger is to do a pivot table. (Data|pivottable) There's an option to show Maximums. L Buchy wrote: I have 2 columns in a spreadsheet. Column 1 has values of (A, B or C), Column 2 has numeric values. In one cell I want to calculate the maximum value for all of rows where column 1 = "A". Example: column 1 column2 A 10 A Max = 15 A 5 B Max = 9.25 B 3 C Max = 3 C 2.5 B 9.25 C 3 A 15 How can I calculate this? -- Dave Peterson |
Calculating Maximums
Yep. Bad answer.
Thanks for the warning. Tom Ogilvy wrote: Might want to take another look at that formula. Regards, Tom Ogilvy Dave Peterson wrote in message ... You could use a formula like: =IF(A1:A7="A",MAX(B1:B7)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Another way when your list of values in column A gets larger is to do a pivot table. (Data|pivottable) There's an option to show Maximums. L Buchy wrote: I have 2 columns in a spreadsheet. Column 1 has values of (A, B or C), Column 2 has numeric values. In one cell I want to calculate the maximum value for all of rows where column 1 = "A". Example: column 1 column2 A 10 A Max = 15 A 5 B Max = 9.25 B 3 C Max = 3 C 2.5 B 9.25 C 3 A 15 How can I calculate this? -- Dave Peterson -- Dave Peterson |
Calculating Maximums
What I may have "meant" was:
=MAX(IF(A1:A7="A",B1:B7)) Still array entered. (ctrl-shift-enter) (all the letters were there, but not in the right order! Oops.) Tom Ogilvy wrote: Might want to take another look at that formula. Regards, Tom Ogilvy Dave Peterson wrote in message ... You could use a formula like: =IF(A1:A7="A",MAX(B1:B7)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Another way when your list of values in column A gets larger is to do a pivot table. (Data|pivottable) There's an option to show Maximums. L Buchy wrote: I have 2 columns in a spreadsheet. Column 1 has values of (A, B or C), Column 2 has numeric values. In one cell I want to calculate the maximum value for all of rows where column 1 = "A". Example: column 1 column2 A 10 A Max = 15 A 5 B Max = 9.25 B 3 C Max = 3 C 2.5 B 9.25 C 3 A 15 How can I calculate this? -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 09:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com