Simple Question

Z

zahoulik

I'm trying to add some sort of cross-check to my spreadsheet. I need t
make a function that compares the sum of a list of numbers and anothe
number (taken from another sheet). I know something lik
=IF(SUM(E5:E9)=E4,"CORRECT","WRONG") can help, but what if I insert
new line of info at E:10? That function cannot help. Any ideas
 
Z

zahoulik

I'm also looking at Conditional Formatting (to avoid having an extra
line in the spreadsheet reading true or false). I now have something
like =SUM(E5:E9)<>E4 in conditional formatting. I'm stuck with the
same error as before. What happens when I insert a new line of data,
say E10, and I want to include that in my conditional formatting? And
is there an easy way to apply the same conditional formatting stuff to
other datasets, such as =SUM(F5:F9)<>F4, etc?
 
J

joeu2004

zahoulik said:
=IF(SUM(E5:E9)=E4,"CORRECT","WRONG") can help,
but what if I insert a new line of info at E:10?
Click to expand...

There may be more clever solutions, but my approach
is to simply ensure that the SUM() range includes blank
(or text) cells above and below the interesting range.
This is often the case anyway, for aesthetic reasons.

Beware: That can lead to confusing results, if you are
not careful. For example, suppose E4 and E10 are
always-blank cells, and you write SUM(E4:E10). That's
okay. But COUNT(E4:E10) gives a misleading result.
You would have to be careful to write COUNT(E4:E10) - 2.
 
A

Ashish Mathur

Hi,

If you are using Office 2003, then you should have ticked "Extend data range
formats and formula" under the edit tab in "options" under the "Tools" menu.

If it is ticked or if you are not using office 2003, then please use the
following array formula (Ctrl+Shift+Enter)

Assume following data is in the range J6:J10 and you have use the sum
funtion in cell J12.

Use the following formual in cell J12,

=SUM(J6:OFFSET(J6,COUNT(J$7:J11),0))

Regards,

Ashish Mathur
 
Top