Summing multi-level bill of material - 51085

All,
   I have a multi-level bill of material and I want to create a
function that will sum it properly without having to sum up each
level
manually. Here is an example:

Level    Item   Quantity   Cost
   1      Chair       1         $39
   2      Seat        1         $15
   3      Cushion   1         $10
   3      Base       1         $5
   2      Leg         4         $1
   2      Back       1         $20
   3      Leather    1         $12
   3      Wood      1         $8

In my example, I have a chair. The chair is composed of a seat ($15),
four legs ($1 each), and a back ($20) for a total of $39. However, the
seat and back are composed of subcomponents. So, my input values
should look like this:

Level    Item   Quantity   Cost
   1      Chair       1
   2      Seat         1
   3      Cushion   1         $10
   3      Base       1         $5
   2      Leg         4         $1
   2      Back       1
   3      Leather    1         $12
   3      Wood      1         $8

And I want excel to figure out the blanks for me using a formula. Can
this be done? Essentially I'm looking for a formula that will sum just
the level below it, until it runs into an equal level, and then it
stops. So, in the case of the seat, it should know to sum the cushion
and the base, but not the leather and the wood. Thanks for the help!
0
Nathan356
5/19/2010 10:54:11 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
1500 Views

Similar Articles

[PageSpeed] 46

Hi Nathan,

This turned out to be more awkward than I expected, and there may be a 
simpler solution.  In the meantime try this.

In D2:

=SUMPRODUCT((OFFSET($A3,,,
MATCH(1,INDEX(($A3:$A$9=$A2)*
(ROW($A3:$A$9)-ROW($A3)),)))=$A2+1)*
OFFSET($D3,,,MATCH(1,INDEX(($A3:$A$9=$A2)*
(ROW($A3:$A$9)-ROW($A3)),)))*
OFFSET($C3,,,MATCH(1,INDEX(($A3:$A$9=$A2)*
(ROW($A3:$A$9)-ROW($A3)),))))

copy down into remaining blank cells.

HTH
Steve D.



"Nathan356" <nathankwok@gmail.com> wrote in message 
news:4cf2dbd1-363a-48a0-80d8-92eb1978a578@y6g2000pra.googlegroups.com...
> All,
>   I have a multi-level bill of material and I want to create a
> function that will sum it properly without having to sum up each
> level
> manually. Here is an example:
>
> Level    Item   Quantity   Cost
>   1      Chair       1         $39
>   2      Seat        1         $15
>   3      Cushion   1         $10
>   3      Base       1         $5
>   2      Leg         4         $1
>   2      Back       1         $20
>   3      Leather    1         $12
>   3      Wood      1         $8
>
> In my example, I have a chair. The chair is composed of a seat ($15),
> four legs ($1 each), and a back ($20) for a total of $39. However, the
> seat and back are composed of subcomponents. So, my input values
> should look like this:
>
> Level    Item   Quantity   Cost
>   1      Chair       1
>   2      Seat         1
>   3      Cushion   1         $10
>   3      Base       1         $5
>   2      Leg         4         $1
>   2      Back       1
>   3      Leather    1         $12
>   3      Wood      1         $8
>
> And I want excel to figure out the blanks for me using a formula. Can
> this be done? Essentially I'm looking for a formula that will sum just
> the level below it, until it runs into an equal level, and then it
> stops. So, in the case of the seat, it should know to sum the cushion
> and the base, but not the leather and the wood. Thanks for the help! 

0
Steve
5/20/2010 8:48:21 AM
Simplified:

=SUMPRODUCT(
(OFFSET($A3,,,MATCH(1,($A3:$A$9=$A2)+1))=$A2+1)*
OFFSET($D3,,,MATCH(1,($A3:$A$9=$A2)+1))*
OFFSET($C3,,,MATCH(1,($A3:$A$9=$A2)+1)))



"Steve Dunn" <stunn@sky.com> wrote in message 
news:EAD30205-67E1-4CEA-841A-DD392C73AC2D@microsoft.com...
> Hi Nathan,
>
> This turned out to be more awkward than I expected, and there may be a 
> simpler solution.  In the meantime try this.
>
> In D2:
>
> =SUMPRODUCT((OFFSET($A3,,,
> MATCH(1,INDEX(($A3:$A$9=$A2)*
> (ROW($A3:$A$9)-ROW($A3)),)))=$A2+1)*
> OFFSET($D3,,,MATCH(1,INDEX(($A3:$A$9=$A2)*
> (ROW($A3:$A$9)-ROW($A3)),)))*
> OFFSET($C3,,,MATCH(1,INDEX(($A3:$A$9=$A2)*
> (ROW($A3:$A$9)-ROW($A3)),))))
>
> copy down into remaining blank cells.
>
> HTH
> Steve D.
>
>
>
> "Nathan356" <nathankwok@gmail.com> wrote in message 
> news:4cf2dbd1-363a-48a0-80d8-92eb1978a578@y6g2000pra.googlegroups.com...
>> All,
>>   I have a multi-level bill of material and I want to create a
>> function that will sum it properly without having to sum up each
>> level
>> manually. Here is an example:
>>
>> Level    Item   Quantity   Cost
>>   1      Chair       1         $39
>>   2      Seat        1         $15
>>   3      Cushion   1         $10
>>   3      Base       1         $5
>>   2      Leg         4         $1
>>   2      Back       1         $20
>>   3      Leather    1         $12
>>   3      Wood      1         $8
>>
>> In my example, I have a chair. The chair is composed of a seat ($15),
>> four legs ($1 each), and a back ($20) for a total of $39. However, the
>> seat and back are composed of subcomponents. So, my input values
>> should look like this:
>>
>> Level    Item   Quantity   Cost
>>   1      Chair       1
>>   2      Seat         1
>>   3      Cushion   1         $10
>>   3      Base       1         $5
>>   2      Leg         4         $1
>>   2      Back       1
>>   3      Leather    1         $12
>>   3      Wood      1         $8
>>
>> And I want excel to figure out the blanks for me using a formula. Can
>> this be done? Essentially I'm looking for a formula that will sum just
>> the level below it, until it runs into an equal level, and then it
>> stops. So, in the case of the seat, it should know to sum the cushion
>> and the base, but not the leather and the wood. Thanks for the help!
> 

0
Steve
5/20/2010 9:53:13 AM
Slight amendment:

=SUMPRODUCT(
(OFFSET($A3,,,MATCH(1,($A3:$A$9=$A2)+{1}))=$A2+1)*
OFFSET($D3,,,MATCH(1,($A3:$A$9=$A2)+{1}))*
OFFSET($C3,,,MATCH(1,($A3:$A$9=$A2)+{1})))



"Steve Dunn" <stunn@sky.com> wrote in message 
news:9039634C-77E7-409B-9E4E-3E5FAA22F338@microsoft.com...
> Simplified:
>
> =SUMPRODUCT(
> (OFFSET($A3,,,MATCH(1,($A3:$A$9=$A2)+1))=$A2+1)*
> OFFSET($D3,,,MATCH(1,($A3:$A$9=$A2)+1))*
> OFFSET($C3,,,MATCH(1,($A3:$A$9=$A2)+1)))
>
>
>
> "Steve Dunn" <stunn@sky.com> wrote in message 
> news:EAD30205-67E1-4CEA-841A-DD392C73AC2D@microsoft.com...
>> Hi Nathan,
>>
>> This turned out to be more awkward than I expected, and there may be a 
>> simpler solution.  In the meantime try this.
>>
>> In D2:
>>
>> =SUMPRODUCT((OFFSET($A3,,,
>> MATCH(1,INDEX(($A3:$A$9=$A2)*
>> (ROW($A3:$A$9)-ROW($A3)),)))=$A2+1)*
>> OFFSET($D3,,,MATCH(1,INDEX(($A3:$A$9=$A2)*
>> (ROW($A3:$A$9)-ROW($A3)),)))*
>> OFFSET($C3,,,MATCH(1,INDEX(($A3:$A$9=$A2)*
>> (ROW($A3:$A$9)-ROW($A3)),))))
>>
>> copy down into remaining blank cells.
>>
>> HTH
>> Steve D.
>>
>>
>>
>> "Nathan356" <nathankwok@gmail.com> wrote in message 
>> news:4cf2dbd1-363a-48a0-80d8-92eb1978a578@y6g2000pra.googlegroups.com...
>>> All,
>>>   I have a multi-level bill of material and I want to create a
>>> function that will sum it properly without having to sum up each
>>> level
>>> manually. Here is an example:
>>>
>>> Level    Item   Quantity   Cost
>>>   1      Chair       1         $39
>>>   2      Seat        1         $15
>>>   3      Cushion   1         $10
>>>   3      Base       1         $5
>>>   2      Leg         4         $1
>>>   2      Back       1         $20
>>>   3      Leather    1         $12
>>>   3      Wood      1         $8
>>>
>>> In my example, I have a chair. The chair is composed of a seat ($15),
>>> four legs ($1 each), and a back ($20) for a total of $39. However, the
>>> seat and back are composed of subcomponents. So, my input values
>>> should look like this:
>>>
>>> Level    Item   Quantity   Cost
>>>   1      Chair       1
>>>   2      Seat         1
>>>   3      Cushion   1         $10
>>>   3      Base       1         $5
>>>   2      Leg         4         $1
>>>   2      Back       1
>>>   3      Leather    1         $12
>>>   3      Wood      1         $8
>>>
>>> And I want excel to figure out the blanks for me using a formula. Can
>>> this be done? Essentially I'm looking for a formula that will sum just
>>> the level below it, until it runs into an equal level, and then it
>>> stops. So, in the case of the seat, it should know to sum the cushion
>>> and the base, but not the leather and the wood. Thanks for the help!
>>
> 

0
Steve
5/20/2010 11:32:29 AM
On May 20, 4:32=A0am, "Steve Dunn" <st...@sky.com> wrote:
> Slight amendment:
>
> =3DSUMPRODUCT(
> (OFFSET($A3,,,MATCH(1,($A3:$A$9=3D$A2)+{1}))=3D$A2+1)*
> OFFSET($D3,,,MATCH(1,($A3:$A$9=3D$A2)+{1}))*
> OFFSET($C3,,,MATCH(1,($A3:$A$9=3D$A2)+{1})))
>
> "Steve Dunn" <st...@sky.com> wrote in message
>
> news:9039634C-77E7-409B-9E4E-3E5FAA22F338@microsoft.com...
>
>
>
> > Simplified:
>
> > =3DSUMPRODUCT(
> > (OFFSET($A3,,,MATCH(1,($A3:$A$9=3D$A2)+1))=3D$A2+1)*
> > OFFSET($D3,,,MATCH(1,($A3:$A$9=3D$A2)+1))*
> > OFFSET($C3,,,MATCH(1,($A3:$A$9=3D$A2)+1)))
>
> > "Steve Dunn" <st...@sky.com> wrote in message
> >news:EAD30205-67E1-4CEA-841A-DD392C73AC2D@microsoft.com...
> >> Hi Nathan,
>
> >> This turned out to be more awkward than I expected, and there may be a
> >> simpler solution. =A0In the meantime try this.
>
> >> In D2:
>
> >> =3DSUMPRODUCT((OFFSET($A3,,,
> >> MATCH(1,INDEX(($A3:$A$9=3D$A2)*
> >> (ROW($A3:$A$9)-ROW($A3)),)))=3D$A2+1)*
> >> OFFSET($D3,,,MATCH(1,INDEX(($A3:$A$9=3D$A2)*
> >> (ROW($A3:$A$9)-ROW($A3)),)))*
> >> OFFSET($C3,,,MATCH(1,INDEX(($A3:$A$9=3D$A2)*
> >> (ROW($A3:$A$9)-ROW($A3)),))))
>
> >> copy down into remaining blank cells.
>
> >> HTH
> >> Steve D.
>
> >> "Nathan356" <nathank...@gmail.com> wrote in message
> >>news:4cf2dbd1-363a-48a0-80d8-92eb1978a578@y6g2000pra.googlegroups.com..=
..
> >>> All,
> >>> =A0 I have a multi-level bill of material and I want to create a
> >>> function that will sum it properly without having to sum up each
> >>> level
> >>> manually. Here is an example:
>
> >>> Level =A0 =A0Item =A0 Quantity =A0 Cost
> >>> =A0 1 =A0 =A0 =A0Chair =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 $39
> >>> =A0 2 =A0 =A0 =A0Seat =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 $15
> >>> =A0 3 =A0 =A0 =A0Cushion =A0 1 =A0 =A0 =A0 =A0 $10
> >>> =A0 3 =A0 =A0 =A0Base =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 $5
> >>> =A0 2 =A0 =A0 =A0Leg =A0 =A0 =A0 =A0 4 =A0 =A0 =A0 =A0 $1
> >>> =A0 2 =A0 =A0 =A0Back =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 $20
> >>> =A0 3 =A0 =A0 =A0Leather =A0 =A01 =A0 =A0 =A0 =A0 $12
> >>> =A0 3 =A0 =A0 =A0Wood =A0 =A0 =A01 =A0 =A0 =A0 =A0 $8
>
> >>> In my example, I have a chair. The chair is composed of a seat ($15),
> >>> four legs ($1 each), and a back ($20) for a total of $39. However, th=
e
> >>> seat and back are composed of subcomponents. So, my input values
> >>> should look like this:
>
> >>> Level =A0 =A0Item =A0 Quantity =A0 Cost
> >>> =A0 1 =A0 =A0 =A0Chair =A0 =A0 =A0 1
> >>> =A0 2 =A0 =A0 =A0Seat =A0 =A0 =A0 =A0 1
> >>> =A0 3 =A0 =A0 =A0Cushion =A0 1 =A0 =A0 =A0 =A0 $10
> >>> =A0 3 =A0 =A0 =A0Base =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 $5
> >>> =A0 2 =A0 =A0 =A0Leg =A0 =A0 =A0 =A0 4 =A0 =A0 =A0 =A0 $1
> >>> =A0 2 =A0 =A0 =A0Back =A0 =A0 =A0 1
> >>> =A0 3 =A0 =A0 =A0Leather =A0 =A01 =A0 =A0 =A0 =A0 $12
> >>> =A0 3 =A0 =A0 =A0Wood =A0 =A0 =A01 =A0 =A0 =A0 =A0 $8
>
> >>> And I want excel to figure out the blanks for me using a formula. Can
> >>> this be done? Essentially I'm looking for a formula that will sum jus=
t
> >>> the level below it, until it runs into an equal level, and then it
> >>> stops. So, in the case of the seat, it should know to sum the cushion
> >>> and the base, but not the leather and the wood. Thanks for the help!-=
 Hide quoted text -
>
> - Show quoted text -

Steve,
    Thanks! That worked perfectly. -Nathan
0
Nathan356
5/20/2010 5:32:04 PM
You're welcome, thanks for the feedback.

0
Steve
5/21/2010 7:00:28 AM
Reply:

Similar Artilces:

Creating separate tables for multi-select list box selections
I have read from some of the other posts that it is necessary to create a separate table for each of the selections made in a multi-select list box from a form. I don't understand this concept! I have a form with 4 multi-select list boxes and many other fields that populate a table with a simple query attached to it. How do I create this separate table for the selections from the list boxes and link it to the main table and query? I am not an Access programmer and am learning as I go along. Please explain step by step and as simply as possible. Thanks for any and all help in a...

Virus?? Cells not summing??
Having a very strange problem on several workstations. Cells are not summing correctly. Let's say cell A4 is a sum of cells A1..A3 It will properly sum the cells the first time but if I go back and change the contents of Cells A1..A3 the new sum will not update? Very strange... I suspect you turned of calculation, either intentionally or with a macro or addin that terminated. Tools, Options, Calculation, automatic If the problem persists you should be aware that it is the first workbook that is opened that controls this, and that is usually your personal.xls So you would c...

Summing a range that changes
Hi, I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help Take the $ out of the equations. $ = absolutes, which tells excell that those particular values do not change. "Jeff" wrote: > Hi, ...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

Form Level Validation Rull Doesn't Recoginize Null
I two dates on a form used to enter and update a table. The following form level validation rule is on the Date Completed field >[Date Received] Or =[Date Received] Or =Null If the user leaves the Date Received blank and enters the Date Completed the validation rule is invoked. If the user then clears the date in the Date Completed with the delete key, the rule is still invoked. How do I have to format the rule so that the user can overcome entering the Date Completed when they didn't intend to, or entered the Date Completed prior to entering the Date Received? Thank you in ...

Bill Reminder #3
In the older versions of Money it used to have a reminder that shows up the upcoming or overdue bills. I couldn't found it on the 2005 Premium version. Is it there ? I setup a bill but it didn't remind me at all. I think you mean Money Express? It stopped working reliably in M2004 and has been removed from M2005. I guess the effort to make it work properly was out of proportion! -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/...

Item level revision numbers on POs
The Item Engineering info on inventory items in manufacturing includes revision numbers for items, however because that info is in the manufacturing dictionary we can't include through report writer on POs. I'd like to develop a routine that would copy the revision number from the item engineering table to one of the user-defined tables in inventory. Any suggestions on how to do that? Or an alternate approach to the problem? -- Jim@TurboChef Jim- This script will put the current Rev level from Item Engineering into User Category 1 on the item master. update iv00101 set usca...

Applying GPOs based on Operating System Version (item-level targetting)
Hi, we have a Win2003 AD with one server acting as the PDC and a small bunch of GPOs. All clients are running WinXP SP3. The whole network / AD is well working. Our management now wants Windows 7, but only on their computers, we're getting a WinXP/Win7 mixed environment (thanks Boss!). Thus we have to split some GPOs (eg. Folder Redirection, etc.). We could clone each security group, one for XP users/computers and the other for Win7 and apply GPOs only to the correct group but that might not be the best solution. We would like to use the item-level targetting like in dri...

Multi Year GP Budget?
GP 10 & FRx 6.7. We are migrating to GP from MAS 500. In MAS, our budget data existed under one header (Current Budget). Each year's budget data was entered under this one Budget name (ID). In GP it appears that one Budget ID based on Fiscal Year can only contain data for one year. Is this correct? The whole point is for FRx column specifications to reference a single Budget ID regardless of the Base fiscal year you are reporting on. Can someone provide GP Budgeting answers? Thanks. You can only have one budget associated with a particular Budget ID. If you want ...

Checking sums won't add up
Hello, Using Excel 2004 for Mac on OS 10.5.8. I'm making a cue sheet which adds up timings of songs. I have a column of 61 entries, all formatted HH:MM:SS (I checked the formatting). I have 3 totals for the column. 1: sum of all entries, =0:59:34 2: sum of "A" songs, =0:35:29 3: sum of "B" songs. =0:24:36 4: sum of "A" total + "B" total =1:00:05. This is a check sum field that should match #1 above, but doesn't. I have checked many times and can't find the problem. If I'm sure that all of the 4 totals above refer to the...

Sum of SQRT(x)
Hi, hope my question isn't too dumb: I want the sum of the square roots of several cells. Writing something like: SUM(SQRT(A1:A10)) or SUM(SQRT(A1):SQRT(A10)) doesn't work. The only solution I could think of was to have another column that calculates SQRT(Ai) and then use SUM on that one. There must be a better way of doing it though, isn't there?? And while we're at it: Is there a general syntax for SUM(f(A1):f(A10)) where f is some function and is the same for all cells to be summed? Thanks in advance! Use =SUMPRODUCT(SQRT(A1:A10)) with simple ENTER Or =SUM(SQRT(A1:A3))...

Using sum and min in the same query
Hi all, In need to build a query that will give the sum of some ammount and will give the % depending on the last period for example 1234 1000 04 10% 1234 2000 07 4% 1234 3000 08 9% 1234 4000 10 8% -------------------------- for the query will get 1234 10000 8%(as for the last month) any ideas? So you want to compare the summed amount for this period against the summed amount for another period in the same table? Use a subquery. This example shows how to sum another period from the same table to get a year-to-date amount: http://allenbrowne.com/subquery-01.html#YTD Use the same prin...

Download Free Study Material for CCNA & MCSE
Best site for CCNA & MCSE Material Tips http://FreeExamKing.com ...

Open Level 2 files
In Outlook 2002, is there anyway to to open level 2 files from outlook? It is a real PITA to save it, find it, then open it. Thanks. ************************************************ Flip the words on the domain name to send email. ************************************************ http://www.slipstick.com/outlook/esecup/getexe.htm should help you out. AFAIK, no, but reading this page might help. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: Curt Bates <crbates@footbig.com> aske...

warcraft power leveling
�й�warcraft gold <http://www.gamegoldvip2.com/warcraftgold.php>ý������Ϊwarcraft gold��˾��warcraft gold������warcraft gold��ҵ�����ҵ�ṩ����warcraft gold���ӹ���˼���������warcraft guide <http://www.blizforum.com>���Ǽ�warcraft guide�ͻ�������warcraft guideý�壬����warcraft guide��Ӫ��Ϊһ������warcraft leveling <http://www.wowgoldvip.com/world-of-warcraft-power-leveling.asp>��˾���Ϻ�warcraft leveling������warcraft leveling������warcraft leveling���������ҹ�˾������warcraft leveling��˾���Ϻ�warcraft leveling��˾�͹���warcraft leveling��˾��ɽwarcraft power leveling��Ϊ�ο��ṩwarcraft power...

Sum of a cell in all worksheets?
I usually just use the search here and quickly find what I need, but can't seem to get it work this morning I am trying to get a sum of a certain cell in all the sheets in workbook. For example of what I mean, Sum(all worksheets, d64). Ca someone possibly help me out Thanks Michae Hi Michael, Look up 3D in your Excel Help. Surely you do not want to include the same sheet. if sheet2 is your second sheet tab and "sheet 24" is the last tab. =SUM(sheet2:'sheet 24'!C14) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: htt...

File level backup of exchange server
Hi We are using an offline backup (via internet). This backup only does a file system backup of the exchange folders. My questions is what are the implications of this sort of backup? Would restoring exchange files be a workable way to restore exchange when we need it? Thanks Regards "John" <info@nospam.infovis.co.uk> wrote in message news:%23yyArBCeKHA.5136@TK2MSFTNGP02.phx.gbl... > Hi > > We are using an offline backup (via internet). This backup only does a > file system backup of the exchange folders. My questions is what are the &...

Item-Level Recover for Public Folders
Hi, i'm testing Exchange 2010 with SC DPM 2010 RC. For item-level restoring MS DPM suggest to use Recovery Database in Exchange 2010. It's works fine with Mailbox Databases. In Exchange we have limit to one Public Folder Mailbox Database. After backup is restoring to Recovery Database, it can not be mounted. If dismount Public Folder Database, Recovery Database bring mounted. Anybody have ideas to implement item-level restore for Exchange Public Folders? If you want to recover an item from a database to another database it is must that both of them need to ...

SUM computed currency
I am getting incorrect sums on a worksheet I am using. What is happening is that I am entering a formula; e.g., .14*7205.73 in a cell that is formatted for currency. Each month, I add another cell with a similar formula. The problem occurs when I SUM the computed cells. Rather than SUM the currency value in the cell, Excel is adding the underlying 4-decimal place value computed by the original formula. To be more exact: Excel computes: 866.3604 + 1010.7468 + 649.7658 = $2526.87 Instead of adding the computed currency values of: 866.36 + 1010.75 + 649.77 = 2526.88 Is there a way to force E...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

FRx Training Materials
I have been tasked with training some users on FRx. What training materials have others used? How long should I plan on spending on the training? Our company does not want to send them out for training, rather they would like me to focus on the report types we use (cash flow, budgets/variances, B/S, I/S, consolidations). We don't expect the users will be creating reports from scratch - they will be following a previously designed format. We have access to the Foundation classes, but I don't know if an (expensive) e-course would also be necessary. If possible, I like to have...

summing text with two conditions from two columns
If somebody can help: I have to summ (in cell K1) number of "Material Releases" from column "I" if I have a letter "Y" in column "J". Also apply condition to color cell if this cell contain a text "PSV", so I can summ it from the column by the reading colors. Second criteria would be if I have one color in a cell then my next reading shfor different text should not apply a new color. Thank you. -- Dan For your first query, try this: =3DSUMPRODUCT((I1:I1000=3D"Material Releases")*(J1:J1000=3D"Y")*(K1:K1000)) I've ...

Another Sum Question
I am using a rather large genomic data set in which I need to classif certain gene functions as one of three things. Each gene (row) ha several ID numbers associated with it. For instance one row could loo like "GO:0005524,GO:0000074,GO:0005730." I need excel to find a match and then add one to an adjacent column (basically tallying). Fo instance, if I search for "GO:0005524" I need excel to search the 100 rows containing this information, and then when it finds a row wit "GO:0005524" to add one to the adjacent column while staying within th same row. That i...

Mail Merge with Access Report (with grouping levels)
Hi, I have two tables in Access 2003 with a one-to-many relationship, in an access report the grouping works, how do I make this work in a Word 2003 mail merge? The following are not my tables but an example of what I am trying to achieve, where the relationship is 1 to many from Orders to OrderItems: Table 1 - Orders OrderID Date SalesPerson Table 2 - OrderItems ItemID Description QTY Value *OrderID On the 1 Page word document I want to print display as follows This is your <Order ID> on <Date>. Your Salesperson was <Salesperson>. Here is a list of your Items: <...

Merging
I'm using Outlook 2003. I have fields at the item level for the contacts and at the form level for my custom form (fields used in my view). Is it possible to merge using form level fields or do I need to recreate all fields I created at the form level now at the folder level to merge with Word? Thanks Shawn See the "Mail Merge with Custom fields" section here; http://www.msoutlook.info/question/275 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more htt...