Results 1 to 6 of 6

20080404, 11:41 #1
 Join Date
 Jan 2004
 Location
 Westervoort, Netherlands
 Posts
 56
 Thanks
 2
 Thanked 0 Times in 0 Posts
test on text value before using sum (2002 SP3)
Hi,
Please see attached file for an example.
I have to use a sum on a column where there is a possibility that one of the cells contains text in stead of numbers. It could happen to all the cells in the range so all cells need to be evaulated.
Also I need the possibility that people add rows to the range without having to update the formula.
I thought of using the ISTEKST function but it does not work on a range. And since the range could grow setting the ISTEKST function for all cells separately is not something I want. Also it makes the formula less readible.
Has anyone got any ideas how I can solve this/
Regards Marcel

20080404, 11:56 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: test on text value before using sum (2002 SP3)
I'd shift the cell with the total one row down, so that there is a blank cell above and below the data.
Enter the following formula for the total as an array formula, i.e. conform with Ctrl+Shift+Enter:
=IF(SUM(1*ISTEXT(B1:B6)),"Not all data entered",SUM(B1:B6))
ISTEXT either returns TRUE=1 or FALSE=0, multiplying by 1 forces Excel to treat the TRUE/FALSE values as numbers. These are added together. If the sum is positive (true), at least one cell contains text, so the warning text is returned, and if the sum is zero (false), no cells contain text, so their sum is returned.
See attached version.

20080404, 13:36 #3
 Join Date
 Jan 2004
 Location
 Westervoort, Netherlands
 Posts
 56
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: test on text value before using sum (2002 SP3)
Hi Hans,
Thanks for the answer.
Can you explain in simple words why this formula only works as an array formula and not as a normal formula?
The advise you give me for an extra row is I think to prevent adding of rows to mess up the formula?
Regards Marcel

20080404, 13:49 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: test on text value before using sum (2002 SP3)
Normally, the ISTEXT function is used for a single cell, e.g =ISTEXT(B2). If you supply a range of cells, e.g. =ISTEXT(B2:B6), the formula will return the result for the first cell in the range only. But if you make it an array formula, it will return an array of as many TRUE/FALSE values as there are cells in the range. You can then manipulate this array, for example to sum the values.
By including a blank cell at the top and bottom of the range to be summed, the formula will adjust itself when cells are inserted in the range (users shouldn't enter data in the top or bottom cell; you could indicate this by giving those cells a gray background, for example).

20080404, 14:09 #5
 Join Date
 Jan 2004
 Location
 Westervoort, Netherlands
 Posts
 56
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: test on text value before using sum (2002 SP3)
Hi Hans,
Thanks for the explanation. I tried the array but without the multiplying and sum as the logical test.
The extra rows is a trick I use often. I just wanted to be sure I did not miss anything.
How do you know these combinations and nested functions. I think I can get around excel pretty good but thinking up these nested functions is beyond me. I sure would like to know if there is course or book where I can learn it.
Thanks Marcel

20080404, 14:31 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: test on text value before using sum (2002 SP3)
The "trick" to build a formula with nested functions is to build it in small steps. I usually create lots of intermediate formulas to test the individual components, then combine the results of these intermediate formulas to build a single more complex one. Sometimes it makes more sense to leave the intermediate formulas because that's easier to maintain. But in this situation it's preferable to have the total formula in a single cell.
See the thread starting at <post:=702,954>post 702,954</post:> for another way of building a complex formula.
John Walkenbach's book Excel 2003 Formulas is a good reference.