Results 1 to 5 of 5

20040928, 22:54 #1
 Join Date
 Jul 2004
 Location
 Sumner, Washington, USA
 Posts
 112
 Thanks
 0
 Thanked 0 Times in 0 Posts
Problems with Array Formulas (Excel XP)
I have a couple array formulas that are not calculating correctly. Actually, the calculations are probably correct, they're just not the ones I want! This is the first:
{(( <font color=blue>R10C12:R10C256</font color=blue> * <font color=448800>RC8</font color=448800> )*SUM( <font color=magenta>RC12:RC256</font color=magenta> ))+( <font color=blue>R10C12:R10C256</font color=blue> *(1RC8))*(SUM( <font color=magenta>RC12:RC256</font color=magenta> *(1+SalEsc)))}
In this formula, <font color=448800>RC8</font color=448800> holds a percentage (percent complete). <font color=blue>R10C12:R10C256 </font color=blue> holds a salary rate. <font color=magenta>RC12:RC256 </font color=magenta> are hours. The formula is supposed to multiply hours times rate times percent complete before the salary escalation date, then add that to hours times the rate at the higher scale times the percent complete after the salary escalation. It works fine for C12, but when you move to C13, it multiplies both sets of hours times the rate in C12 instead of each by their corresponding rate. I think. I suspect it is something glaringly obvious, I just can't see it.
The second formula, since it is based on the first, has the same problem of not adjusting correctly for more than one column of data. But it has another problem. Is it possible to construct an array formula that evaluates an If statement within the array? For example, this is the relevant part of the formula I'm using now (remember, it isn't working in the same way the one above isn't working, so that has to be fixed first):
{(SUM(RC8:RC256)*(<font color=red>R9</font color=red>C8:<font color=red>R9</font color=red>C256)+(SUM(RC8:RC256)*<font color=red>R9</font color=red>C8:<font color=red>R9</font color=red>C256)}
I need to modify it so that if <font color=red>R9</font color=red> is blank, it uses the value in R10 instead. According to my book, it seems like maybe a SUMIF might work, but I don't have any idea of how to do it. Thanks!
Oh, in case it's relevant, below is the entire formula as it currently exists:
{IF(InhouseMarkup=0,(SUM(RC8:RC256)*<font color=red>R9</font color=red>C8:<font color=red>R9</font color=red>C256),(SUM(RC8:RC256)*<font color=red>R9</font color=red>C8:R9C256)+(SUM(RC8:RC256)*<font color=red>R9</font color=red>C8:<font color=red>R9</font color=red>C256)*InhouseMarkup)}
And the final version, besides evalulating whether or not <font color=red>R9</font color=red> was blank, would have to multiply by OutsideMarkup instead of InhouseMarkup.
As always, many thanks!
Karyl

20040928, 23:11 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Problems with Array Formulas (Excel XP)
Shouldn't each rate be multiplied by hours before calculating the sum?
It would be easier if you posted the formulas in A1 format instead of R1C1 format, for almost everybody uses A1 format. Or even better, attach a workbook, so that we don't have to guess what all those formulas refer to.

20040929, 05:49 #3
 Join Date
 Jul 2004
 Location
 Sumner, Washington, USA
 Posts
 112
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Problems with Array Formulas (Excel XP)
<< Shouldn't each rate be multiplied by hours before calculating the sum? >>
Yeah, I guess that would make a difference, huh? :)
I said it was bound to be some glaringly obvious goof. Too bad these kinds of things aren't obvious to me, and I think it's a bit late to hope I'll develop an aptitude for numbers! At least I know enough to always triple check the results to make sure what I think should be happening really is, and, whenever possible, to get someone else to check my math and confirm my assumptions. Thanks!
For those reading along, this is the fixed formula: {SUM(BurdenedRate*$L12:$IV12)*($H12)+SUM(BurdenedR ate*$L12:$IV12)*(1$H12)*(1+SalEsc)}
I've included the Labor sheet in the attached sample if you want to see it.
So, on to the next problem. I've fixed the array formula in Column F on the Expenses sheet in the attached workbook so that it is working correctly for inhouse charges (basically a simplified version of the same formula I used for calculating labor). But when it is an outside vendor, I don't know how to adjust the formula to multiply using the amount in Row 10 instead of Row 9.
Sorry about not sending a sample sheet with the first message. I wasn't sure I'd be able to get back to this tonight, and I wanted to make a try for an answer before morning. And the clock was moving way too fast! My daycare charges $1 per minute per kid if I'm late ($120 an hour!), which is a HECK of a lot more than I get paid for staying overtime ($0)!
Karyl
EDIT: Replaced attachment.

20040929, 06:41 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Problems with Array Formulas (Excel XP)
I think this is the formula you want for cell F12:
=SUM(($H12:$IV12)*(InhouseCost))*(1+InhouseMarkup) +SUM(($H12:$IV12)*(OutsideCost))*(1+OutsideMarkup)
entered as an array formula. The IF you had is not recessary: if the markup is 0, the multiplication factor is 1+0 = 1, so that is OK. I just added the inhouse and outside vendor costs together; if one of them is absent it will just contribute 0.

20040929, 15:55 #5
 Join Date
 Jul 2004
 Location
 Sumner, Washington, USA
 Posts
 112
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Problems with Array Formulas (Excel XP)
Thanks, Hans. As always, it works perfectly!
It's those little things, like not needing the IF's because zero results don't contribute to the total, that drive me nuts. It makes perfect sense when someone points it out, but I never just "see" it myself. I keep hoping that since I have been working in Excel as much as I have been, some of this will start to become easier, but, so far, it is still a real stretch. I have adapted to using R1C1 references, however, after reading about the benefits of doing so in Excel: Best Practices for Business. And that was a real transition for me. So maybe the rest of this will come with time. . .
Many thanks!