Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a value Using a lookup table multiple columns and rows | Excel Worksheet Functions | |||
find number in multiple columns and rows | Excel Worksheet Functions | |||
find and delete a character from multiple columns | Excel Programming | |||
Combining find with clearcontents on multiple columns | Excel Programming | |||
Find Values 0 Over Multiple Columns | Excel Programming |