|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 4th, 2004, 01:25 PM
|
|||
|
|||
calculations
I have a file like
Dept PayType Amount 2312 OT 500 reg 1000 vac 10 ss 5 2566 ot 5522 reg 42 vac 565 etc 2556 reg 10 I would like to automate the process of cumulating the amounts. For eg: I need to sum "reg" and "ot" for "2312" and "2556" only. I think the logic should go something like this - look in paytype and find "reg" or "ot", if found, move left and if dept is "blank", go up until you get a value. Now, if that is "2312" or "2556", sum"amount". I am lost coding this. please help Thanks .. 
|
|
#2
October 4th, 2004, 01:38 PM
|
|||
|
|||
|
Hi
could you fill column A in all rows with the dep. ID this way you could use SUMPRODUCT for this. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany wrote: I have a file like Dept PayType Amount 2312 OT 500 reg 1000 vac 10 ss 5 2566 ot 5522 reg 42 vac 565 etc 2556 reg 10 I would like to automate the process of cumulating the amounts. For eg: I need to sum "reg" and "ot" for "2312" and "2556" only. I think the logic should go something like this - look in paytype and find "reg" or "ot", if found, move left and if dept is "blank", go up until you get a value. Now, if that is "2312" or "2556", sum"amount". I am lost coding this. please help Thanks .
|
|
#3
October 4th, 2004, 02:06 PM
|
|||
|
|||
|
I want to avoid manually filling in the rows. I would
like to do either of 2 things. 1. Automate the process of filling in the Dept if the field below is empty or 2. For a particular amount, go left, get Paytype and then left to get dept, if dept field is empty, go up until you come accross a value. Thanks -----Original Message----- Hi could you fill column A in all rows with the dep. ID this way you could use SUMPRODUCT for this. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany wrote: I have a file like Dept PayType Amount 2312 OT 500 reg 1000 vac 10 ss 5 2566 ot 5522 reg 42 vac 565 etc 2556 reg 10 I would like to automate the process of cumulating the amounts. For eg: I need to sum "reg" and "ot" for "2312" and "2556" only. I think the logic should go something like this - look in paytype and find "reg" or "ot", if found, move left and if dept is "blank", go up until you get a value. Now, if that is "2312" or "2556", sum"amount". I am lost coding this. please help Thanks . .
|
|
#4
October 4th, 2004, 02:33 PM
|
|||
|
|||
|
If you follow Frank's suggestion and fill Column A with department ID's,
you can use the following formula to sum 'ot' and 'reg' amounts for departments 2312 and 2556... =SUMPRODUCT((A2:A9={2312,2556})*ISNUMBER(MATCH(B2: B9,{"ot","reg"},0))*C2: C9) OR =SUMPRODUCT((A2:A9={2312,2556})*ISNUMBER(MATCH(B2: B9,E2:E3,0))*C2:C9) ....where E2 contains the first PayType of interest, and E3 the second. Hope this helps! In article , wrote: I have a file like Dept PayType Amount 2312 OT 500 reg 1000 vac 10 ss 5 2566 ot 5522 reg 42 vac 565 etc 2556 reg 10 I would like to automate the process of cumulating the amounts. For eg: I need to sum "reg" and "ot" for "2312" and "2556" only. I think the logic should go something like this - look in paytype and find "reg" or "ot", if found, move left and if dept is "blank", go up until you get a value. Now, if that is "2312" or "2556", sum"amount". I am lost coding this. please help Thanks .
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Duration calculations and a default start date | Duncan Edment | General Discussion | 1 | August 12th, 2004 01:12 PM |
| rounding making result of calculations look incorrect | Helen | Running & Setting Up Queries | 6 | May 29th, 2004 02:29 PM |
| auto calculations | thaddeus | Worksheet Functions | 2 | October 28th, 2003 02:05 AM |
| List Calculations | Wayne Burritt | Worksheet Functions | 2 | October 10th, 2003 04:17 PM |
Linear Mode
