Formula Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jason - 21 Mar 2008 14:28 GMT

Can anybody tell me why they think this formula isn't working. I have
similar formulas in adjacent worksheets that work fine.

=SUMPRODUCT((E25:E5000)*(F25:F5000=9)*(G25:G5000=6.5))

I get the #VALUE! error.

Reply to this Message

Gary''s Student - 21 Mar 2008 14:42 GMT

You do not have a formula problem. You probably have a data problem. For
example, if E25 thru G30 contain:

1    0    0
1    0    0
1    9    0
2    9    6.5
1    0    0
1    0    0

Your formula correctly returns 2

Signature

Gary''s Student - gsnu2007g

> Can anybody tell me why they think this formula isn't working. I have
> similar formulas in adjacent worksheets that work fine.
>
> =SUMPRODUCT((E25:E5000)*(F25:F5000=9)*(G25:G5000=6.5))
>
> I get the #VALUE! error.

Reply to this Message

Tom Hutchins - 21 Mar 2008 14:50 GMT

Your data problem may be a cell in column E which contains text instead of a
number.

Hope this helps,

Hutch

> You do not have a formula problem. You probably have a data problem. For
> example, if E25 thru G30 contain:
[quoted text clipped - 14 lines]
> >
> > I get the #VALUE! error.

Reply to this Message

Jason - 21 Mar 2008 17:28 GMT

It is a small amount of data. Here is the actual data I'm trying to
use: Columns are E,F and G. Column E is time.

Thanks

0:46    9     $6.5
0:45    11     $6.5
0:45    9     $6.5
0:46    9     $6.5
0:47    10     $6.5

0:47    9     $6.5
0:48    9     $6.5
0:48    9     $6.5
0:36    1     $6.5
0:48    9     $6.5
0:45    10     $6.5

0:54    9     $6.5
0:48    9     $6.5
0:49    9     $6.5

>Your data problem may be a cell in column E which contains text instead of a
>number.
[quoted text clipped - 21 lines]
>> >
>> > I get the #VALUE! error.

Reply to this Message

GoBow777 - 21 Mar 2008 20:13 GMT

'Jason[_12_ Wrote:

> ;642351']Can anybody tell me why they think this formula isn't working.
> I have
[quoted text clipped - 3 lines]
>
> I get the #VALUE! error.

No idea why you’re getting the VALUE# error.

To see how Excel is interpreting your formula, click Tools/Formula
Auditing/Evaluate Formula.

Signature

GoBow777

Reply to this Message

Jason - 21 Mar 2008 21:29 GMT

I don't see a "tools." (Excel 2007)

But I did discover that the formula will work until I get to the empty
row. If I include data up to the first empty row it works fine. If I
try to include data that goes across the empty rows it fails.
Strange, because the exact same formula works on other spreadsheets.

>'Jason[_12_ Wrote:
>> ;642351']Can anybody tell me why they think this formula isn't working.
[quoted text clipped - 9 lines]
>To see how Excel is interpreting your formula, click Tools/Formula
>Auditing/Evaluate Formula.

Reply to this Message

Jason - 22 Mar 2008 00:56 GMT

I fixed it by doing the following in the E column:

=IF(D28="",0,D28-C28)

As opposed to:

=if(D28="","",D28-C28)

>I don't see a "tools." (Excel 2007)
>
[quoted text clipped - 16 lines]
>>To see how Excel is interpreting your formula, click Tools/Formula
>>Auditing/Evaluate Formula.

Reply to this Message