Sum multiple columns; find max
 

I want to be able to do this in a macro so that it is
invisible to the user. User enters 3 pieces of
data in a new column each week:

wk 1 wk 2 wk 3 ......
OT hours
Repair hours
Cost

I want to be able to sum wk 1 (OT hours + Repair
hours) and then wk 2 (OT hours + Repair
hours) and find the MAX for all weeks input
but I do not want to add another row of data to the
spreadsheet. I want this to be invisible
to the user. Can someone help me achieve this?

Sum multiple columns; find max
 

Opal-

This can be done with VBA, but let me propose an easier alternative: array
formulas. Assuming your grid has data in columns B,C,D and rows 2,3,4:

=MAX((B2:D2+B3:D3+B4:D4))

then while still in the formula, press Cntrl-shift-enter to make it an array
formula. If this is done correctly, it will show on the formula bar as:
{=MAX((B2:D2+B3:D3+B4:D4)) }

you can't enter the { } yourself, Excel will add them when you press
ctrl-shift-enter.

HTH,
Keith


"Opal" wrote:

I want to be able to do this in a macro so that it is
invisible to the user. User enters 3 pieces of
data in a new column each week:

wk 1 wk 2 wk 3 ......
OT hours
Repair hours
Cost

I want to be able to sum wk 1 (OT hours + Repair
hours) and then wk 2 (OT hours + Repair
hours) and find the MAX for all weeks input
but I do not want to add another row of data to the
spreadsheet. I want this to be invisible
to the user. Can someone help me achieve this?