Rounding
in [Functions]
|
Prev: Office Project vs. Office Web Component
Next: How do I print my spreadsheet to show the formulas of a cell?
From: Curtis on 22 Feb 2010 13:20 I have 2 values which I know equal however when I use the round function below it states it does not equal =IF(ROUND(BH3,3)<>SUMPRODUCT(--('Jan Data'!$F$3:$F$12230)=$B3)*('Jan Data'!$N$3:$N$12230=FALSE)*('Jan Data'!$I$3:$I$12230),"not balanced","") What am I doing wrong thanks
From: Curtis on 22 Feb 2010 13:28 Disregard....Figured it out...Thanks "Curtis" wrote: > I have 2 values which I know equal however when I use the round function > below it states it does not equal > > =IF(ROUND(BH3,3)<>SUMPRODUCT(--('Jan Data'!$F$3:$F$12230)=$B3)*('Jan > Data'!$N$3:$N$12230=FALSE)*('Jan Data'!$I$3:$I$12230),"not balanced","") > > What am I doing wrong > > thanks
From: Duke Carey on 22 Feb 2010 13:28 You should round() the sumproduct(), too, to the same number of decimals to troubleshoot this on your own you could put your sumproduct formula in a cell by itself and set the format to something with well over 3 decimals and you'd almost certainly find that it is not equal to the comparison value. "Curtis" wrote: > I have 2 values which I know equal however when I use the round function > below it states it does not equal > > =IF(ROUND(BH3,3)<>SUMPRODUCT(--('Jan Data'!$F$3:$F$12230)=$B3)*('Jan > Data'!$N$3:$N$12230=FALSE)*('Jan Data'!$I$3:$I$12230),"not balanced","") > > What am I doing wrong > > thanks
From: Fred Smith on 22 Feb 2010 13:27
1. Your parentheses are out of order. Try: =IF(ROUND(BH3,3)<>SUMPRODUCT(('Jan Data'!$F$3:$F$12230=$B3)*('Jan Data'!$N$3:$N$12230=FALSE)*('Jan Data'!$I$3:$I$12230)),"not balanced","") (BTW, in a Sumproduct, you either multiply with *, or use unary --, not both) 2. I would think that you need to Round the Sumproduct result as well. Regards, Fred "Curtis" <Curtis(a)discussions.microsoft.com> wrote in message news:A4B3AC1F-8A56-44A5-B5BC-CC81798D4613(a)microsoft.com... >I have 2 values which I know equal however when I use the round function > below it states it does not equal > > =IF(ROUND(BH3,3)<>SUMPRODUCT(--('Jan Data'!$F$3:$F$12230)=$B3)*('Jan > Data'!$N$3:$N$12230=FALSE)*('Jan Data'!$I$3:$I$12230),"not balanced","") > > What am I doing wrong > > thanks |