help please  
Author Message
TrinhDinhNguyen





PostPosted: Wed Oct 03 17:20:02 PDT 2007 Top

worksheet functions >> help please

I need to create a function that will display an error if the sum of a range
of cells subtracted from another cell does not equal 0. I figure the first
part is
=SUM((J12:M12)-I12) that should total 0 and if it does not I need error to
be shown. Any advice/help greatly appreciated

Excel515  
 
 
Eva





PostPosted: Wed Oct 03 17:20:02 PDT 2007 Top

worksheet functions >> help please try this
=IF(SUM(J12:M12)-I12<=0,"Error",SUM(J12:M12)-I12)
Eva


> I need to create a function that will display an error if the sum of a range
> of cells subtracted from another cell does not equal 0. I figure the first
> part is
> =SUM((J12:M12)-I12) that should total 0 and if it does not I need error to
> be shown. Any advice/help greatly appreciated
 
 
kara





PostPosted: Wed Oct 03 17:29:01 PDT 2007 Top

worksheet functions >> help please many thanks eva!



> try this
> =IF(SUM(J12:M12)-I12<=0,"Error",SUM(J12:M12)-I12)
> Eva

>
> > I need to create a function that will display an error if the sum of a range
> > of cells subtracted from another cell does not equal 0. I figure the first
> > part is
> > =SUM((J12:M12)-I12) that should total 0 and if it does not I need error to
> > be shown. Any advice/help greatly appreciated
 
 
David





PostPosted: Thu Oct 04 07:11:33 PDT 2007 Top

worksheet functions >> help please Doesn't that show "Error" is the answer *does* equal zero?
Did you mean to say
=IF(SUM(J12:M12)-I12<>0,"Error",SUM(J12:M12)-I12) ?

That would work OK if the numbers are integers, but if you have decimal
numbers (such as currency with 2 decimal places) you may find that roundings
of the fixed point binary representations may give a small but non-zero
result where you expected zero. If you want to eliminate such cases you may
want something like
=IF(ABS(SUM(J12:M12)-I12)<10^-6,"Error",SUM(J12:M12)-I12)
Adjust the error tolerance to suit.
--
David Biddulph



> try this
> =IF(SUM(J12:M12)-I12<=0,"Error",SUM(J12:M12)-I12)
> Eva

>
>> I need to create a function that will display an error if the sum of a
>> range
>> of cells subtracted from another cell does not equal 0. I figure the
>> first
>> part is
>> =SUM((J12:M12)-I12) that should total 0 and if it does not I need error
>> to
>> be shown. Any advice/help greatly appreciated