Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
I have a sheet that lists items and sales for each month for the last 24
months. Example Description, Oct04, Nov04,....Aug,06,Sep06 Item001, 1, 0,.....,0,0 Item002, Item003 I need a formula to determine the last month the sales number was positive. For Item001 the result would be Oct04. Grant |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
One way ..
Assuming the 24 date col headers are in B1:Y1, data from row2 down Put in Z2, array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(SUM(B2:Y2)=0,"",INDEX($B$1:$Y$1,MATCH(MAX((B2: Y20)*COLUMN(B2:Y2)),(B2:Y20)*COLUMN(B2:Y2),0))) Copy Z2 down to return the required results .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Grant" wrote: I have a sheet that lists items and sales for each month for the last 24 months. Example Description, Oct04, Nov04,....Aug,06,Sep06 Item001, 1, 0,.....,0,0 Item002, Item003 I need a formula to determine the last month the sales number was positive. For Item001 the result would be Oct04. Grant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |