Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Filter by formula
From: bob on 20 May 2010 11:57
From Sheet 2, I'd like a formula to calculate the following using an array:

If Sheet1 Column A = Sheet2 Column A
AND
If Sheet1 Column K > 0
THEN
Divide Sheet1 Column K by Sheet1 Column L

Can anyone help? Thanks.

Bob

From: Mike on 20 May 2010 12:09
=IF(A1=Sheet1!A1,IF(Sheet1!K1>0,Sheet1!K1/Sheet1!L1,0))

"bob" wrote:

> From Sheet 2, I'd like a formula to calculate the following using an array:
>
> If Sheet1 Column A = Sheet2 Column A
> AND
> If Sheet1 Column K > 0
> THEN
> Divide Sheet1 Column K by Sheet1 Column L
>
> Can anyone help? Thanks.
>
> Bob
>
From: Luke M on 20 May 2010 15:51
Assuming you are wanting a single-cell output, this array* formula will
work:

=SUM(IF((A2:A100=Sheet2!A2:A100)*(K2:K100>0),K2:K100)/Sheet2!L2:L100)

Note that if any cell in L2:L100 is text or equal to zero, an error will
occur.
*Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter

--
Best Regards,

Luke M
"bob" <bob(a)discussions.microsoft.com> wrote in message
news:12C5DC18-9164-41C5-BF46-2354CB932FC3(a)microsoft.com...
> From Sheet 2, I'd like a formula to calculate the following using an
> array:
>
> If Sheet1 Column A = Sheet2 Column A
> AND
> If Sheet1 Column K > 0
> THEN
> Divide Sheet1 Column K by Sheet1 Column L
>
> Can anyone help? Thanks.
>
> Bob
>


 | 
Pages: 1
Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Filter by formula