0

I have a spreadsheet and want to find the MAX value in a column, but only if the number in another column equals say 1 for instance. What formula would I need to use?

For example I have 10 rows in column A the max number is 40 and the lowest is 13. In column B are various numbers from 1-5, and 4 of them are 1's. In want to know what the Max number is in column A, on the 4 1's. I could do this by filtering for simple info like this, but I need to know for 1000's of rows.

Any help would be great.

2
  • What have you tried/researched? Is there a formula you are working with? If you post it we can help you find out why it isn't giving you the results you desire.
    – CharlieRB
    Oct 30, 2013 at 19:23
  • Thanks for replying. I tried to use the following formula, but was unsure it is the correct one (probably not). =MAX(IF(cellrange=cell/number(cellrange)) it keeps coming back with zero.
    – Mark
    Oct 30, 2013 at 19:28

2 Answers 2

1

Now that I understand better what you are trying to do, here is the answer I believe you are looking for (I am leaving the other answer in case it is useful to someone else).

The following is an array formula. Array formulas let you perform multiple calculations in a single sell. There is a specific way to enter them. You can not just type the curly brackets at the end. You must first type the formula, then press the key combination of Ctrl+Shift+Enter. This tells Excel it is an array formula.

{=MAX(IF(A:A=D1,B:B, ))}

What I did here was to use D1 as a way for you to enter the number you are looking for. Then the formula compares this to column A to find the entries matching it. Then it looks in column B for the MAX number within the matches.

To look for the max of a different number, type a different number in D1. You can use whatever cell you want in place of D1 as long as you change the formula. Or, you can simply put the number you want directly into the formula (A:A=1).

2
  • Sorry it has taken so long to reply, I tested the formula and it worked. Thank you so much for help. It is much appreciated.
    – Mark
    Nov 14, 2013 at 15:29
  • Glad to hear that it helped you. If this is the answer to your question, please click the check mark on the left to mark it as such. Then others will see the question has a working answer.
    – CharlieRB
    Nov 14, 2013 at 17:42
0

You are pretty close.

Use the IF function to make a logical check for the number "1", then use the MAX function to find the maximum number in a range if the IF finds it to be TRUE. Otherwise, if it finds it to be FALSE it will do nothing.

=IF(B1=1, MAX(A1:A10), "")

Sources;
IF function
MAX function

6
  • I tried the above formula and it gave me the answer you suggested FALSE, I need the answer to show the MAX value. Do if everything in cell B is 1, and the highest value in cell A is random number but the highest is 40, I need the figure to come back 40. Is this a different formula. Sorry for the confusion.
    – Mark
    Oct 31, 2013 at 9:50
  • It worked for me with the explanation of data you gave. Where are you placing the formula? It can not be in column A or B because it can not check itself. I don't understand what you mean "everything in cell B is 1". Do you mean all cells in column B have to equal "1"? Can you give an screenshot example of your work?
    – CharlieRB
    Oct 31, 2013 at 12:50
  • I can't show what I am working on it is confidential data. I will try and explain a bit better. If column A number of planks of wood- ten 1's two 3's and four 2's. In Column B it has random sizes from 1m to 20m. I want a formula to find the longest plank (MAX), if the the number in column A is equal to one. So it's looks at every number in the column if it's a 1 then I want to find the highest number in column B. If I am not explaining myself properly, I am really sorry. I can only do it at work as I have no excel at home. Thanks for your help by the way.
    – Mark
    Oct 31, 2013 at 19:50
  • The way you are explaining it, you will always have the MAX number in the entire column regardless the value of 1. Are you saying you want to find the MAX number for ONLY the rows which have a 1 in column A? If that's the case, we need to use a different formula.
    – CharlieRB
    Nov 1, 2013 at 11:04
  • Yes, sorry really crap at explaining things and I thank you for your help.
    – Mark
    Nov 1, 2013 at 18:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .