Finding Max Within 3 Columns

C

ClareMarie

For each row I need to determine which of 3 columns has the max numbe
of hours and then populate Column A with the appropriate indicator.

Example of desired results:


MaxHours INVESTMENT CORE MISC
CORE 80 100 3

I am new to Excel macros/VBA and would appreciate any assistance.

Thanks,

Clare Marie
:cool
 
F

Frank Kabel

Hi
a non VBA solution. Put the following formula in cell A2
=INDEX($B$1:$D$1,1,MATCH(MAX(B2:D2),B2:D2,0))
and copy this down
 
C

ClareMarie

Thanks for the solution. It works perfectly for what I asked.

However, now I find that I need to populate ColA with just a one-lette
Indicator based on the Column name which contained the maximum amoun
of hours, not the Column name itself
 
C

ClareMarie

Hi Frank,

The 3 Columns containing hours are:

Core, Investment, and KPHC

Based on which column contains max hours, Col A will contain
either a 'C', 'I' or 'K' (using first letter of the column name).

This logic is needed in addition to the formula you provided in you
first post.

Many thanks for your continued help.

Clare Mari
 
C

ClareMarie

This works great and I used the instructions from the 'Help
documentation to propagate this formula for the entire column, with th
exception of the header cell. It works but gives me an N/A in th
Indicator Column if the row isn't populated.

Is there a more effective way of inserting a formula throughout th
column which would keep the cell empty rather than populate it with a
N/A?


Thanks,

Clare Mari
 
Top