Excel function: how to 'calculate' the highest value in a column based upon value in another column

[edprush]

Here I go, trying to make sense...

I have two columns of data: A and B

Column A is a list of ages, column B is a count of people with that respective age.

In Excel, how would I find the greatest age in column A that has at least one person of that age?


Please ignore the dashes in the 'spreadsheet' below:

Age-------- Qty.
A------------- B
61----------- 14
62 ----------- 5
63 ------------1
64------------ 1
65 ----------- 1
66 ----------- 1
67 ------------1
68 ------------2
69 ------------1
70 ------------1
71 ------------0
72 ------------0
73 ------------0

The answer should be: 70

 

[Smilin]

ok, Make a subtle change to the spreadsheet.

Starting at A1, make it look like so:


AGE QTY
.... >0
AGE QTY
61 15
62 5
63 1

and so on...
You have basically shifted your column header down to row 3 then added two rows at the top. The first row is a duplicate of the column headers on row 3, and row 2 simply has the text ">0" in B2

Then in another cell, drop this formula:

=DMAX(A3:Bxx,"Age",A1:B2)

...where XX is the end row of your data.

 

[PowerEngineer]

The easiest way might be to add a column C, where the entry is each cell is:

=IF(B>0,A,0)

and then find the MAX of column C.

 

[edprush]

Originally posted by: Smilin
ok, Make a subtle change to the spreadsheet.

Starting at A1, make it look like so:


AGE QTY
.... >0
AGE QTY
61 15
62 5
63 1

and so on...
You have basically shifted your column header down to row 3 then added two rows at the top. The first row is a duplicate of the column headers on row 3, and row 2 simply has the text ">0" in B2

Then in another cell, drop this formula:

=DMAX(A3:Bxx,"Age",A1:B2)

...where XX is the end row of your data.

friggen' genius!

It works and I still don't understand it.

Thanks smilin and PowerEngineer too!

 

[edprush]

What would be the best way to find the average age? Again, I only want to count ages that have a person (or people).

I've figured out how to display the average age by creating an additonal column and multiplying each age by the respective Qty. Then dividing that by the total Qty. But I was wondering if there is a way to do this without adding a column.

Would this be some type of weighted average function in Excel?

 

[Kyteland]

sumproduct(A1:Ax,B1:Bx)/sum(B1:Bx)

 

[edprush]

Originally posted by: Kyteland
sumproduct(A1:Ax,B1:Bx)/sum(B1:Bx)

dag, you guys are awesome!

 

[Smilin]

Originally posted by: edprush
friggen' genius!

It works and I still don't understand it.

Thanks smilin and PowerEngineer too!

yeah, FYI I don't understand it either. I just munged it together

 

[Kyteland]

Originally posted by: Smilin

Originally posted by: edprush
friggen' genius!

It works and I still don't understand it.

Thanks smilin and PowerEngineer too!

yeah, FYI I don't understand it either. I just munged it together

All of the functions that start with D are database functions. the arguments are:
DMAX(Database, Field, Criteria)
Database - A range of cells is considered a database when the first row contains labels for the columns and the columns contian data.
Field - Either the specific name of a column or an offset number in the database that indicates the column to be used in the function.
Criteria - A range of cells that contain conditions that need to be met for the function to be applied. These ate listed by a column label with a condition directly below it.

AGE QTY
.... >0
AGE QTY
61 15
62 5
63 1

=DMAX(A3:Bxx,"Age",A1:B2)

So your function is MAX(), your database is A3:Bxx, "Age" is the column you want to take the MAX() of and "QTY >0" is the condition that has to apply before AGE will be considered for the MAX() function. The function actually could be slightly simpler. =DMAX(A3:Bxx,"Age",B1:B2) You aren't using AGE as a condition, so there's no reason to include it in your criteria list.

 

[edprush]

Kyteland, thanks for taking the time to explain that function to me.

I was really stumped regarding how the criteria range worked. I didn't understand how the text above the numbers was being utilized in the 'calculation' but I see that it is interpreted as a column label.


Thanks again.