Prev: multiple calculations in 1 cell
Next: Multiple rate of pays
From: catts22 on 5 Mar 2010 16:12
Here is my formula - it ends up with FALSE - I must have a ) in the wrong
place can anyone help?

Essentially I don't want to end up with a ##### (negative number result) so
I came up with this formula. If there is a way to make the ##### actually
look like a proper negative number or blank that would be even better.

Thanks



=IF(IF(AND(BY20>0,BY20-CA20>0),SUMPRODUCT(('Incidents
2010'!$C$20:$C$6998="good")*('Incidents
2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incidents
2010'!$N$20:$N$6998=3)*('Incidents
2010'!$O$20:$O$6998>=DATE(2010,1,1))*('Incidents
2010'!$O$20:$O$6998<DATE(2010,2,1)),'Incidents
2010'!$I$20:$I$6998)/(BY20-CA20))>0,IF(AND(BY20>0,BY20-CA20>0),SUMPRODUCT(('Incidents
2010'!$C$20:$C$6998="good")*('Incidents
2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incidents
2010'!$N$20:$N$6998=3)*('Incidents
2010'!$O$20:$O$6998>=DATE(2010,1,1))*('Incidents
2010'!$O$20:$O$6998<DATE(2010,2,1)),'Incidents
2010'!$I$20:$I$6998)/(BY20-CA20),""))
From: Fred Smith on 5 Mar 2010 19:25
Remove the double IF at the start.

Regards,
Fred

"catts22" <catts22(a)discussions.microsoft.com> wrote in message
news:55D9A81C-E2A6-43BF-8780-B45E4A37D22F(a)microsoft.com...
> Here is my formula - it ends up with FALSE - I must have a ) in the wrong
> place can anyone help?
>
> Essentially I don't want to end up with a ##### (negative number result)
> so
> I came up with this formula. If there is a way to make the ##### actually
> look like a proper negative number or blank that would be even better.
>
> Thanks
>
>
>
> =IF(IF(AND(BY20>0,BY20-CA20>0),SUMPRODUCT(('Incidents
> 2010'!$C$20:$C$6998="good")*('Incidents
> 2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incidents
> 2010'!$N$20:$N$6998=3)*('Incidents
> 2010'!$O$20:$O$6998>=DATE(2010,1,1))*('Incidents
> 2010'!$O$20:$O$6998<DATE(2010,2,1)),'Incidents
> 2010'!$I$20:$I$6998)/(BY20-CA20))>0,IF(AND(BY20>0,BY20-CA20>0),SUMPRODUCT(('Incidents
> 2010'!$C$20:$C$6998="good")*('Incidents
> 2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incidents
> 2010'!$N$20:$N$6998=3)*('Incidents
> 2010'!$O$20:$O$6998>=DATE(2010,1,1))*('Incidents
> 2010'!$O$20:$O$6998<DATE(2010,2,1)),'Incidents
> 2010'!$I$20:$I$6998)/(BY20-CA20),""))

 | 
Pages: 1
Prev: multiple calculations in 1 cell
Next: Multiple rate of pays