Joe100

I have a spreadsheet with 10 columns. In the 11th column I want the word error to appear if the data in any of the 10 columns is not a number, or is a number less than or equal to 0.

Would the best way to do this be to write a function for column 11, or use an if statement?

Michael_I

Does it need to be done through code? You could enter a couple of if statements in a formula in column 11. If through code, then I would use a For/Next function for each column 1-10.

Kluz

To do this with a cell equation you will need to use an array formula. Assuming the first 10 columns contain your data and they start at row 1 enter this equation into the formula bar while cell K1 is selected:Instead of hitting just 'Enter' hit Ctrl + Shift + Enter you should see in the formula bar squiggle brackets: {} around the above cell equation.

Joe100

Thank you both for your replies. I tried the iserror array formula and it didn't seem to work for me. Perhaps I was using it wrong. I am going to pose a different question to see if I can find out what I am looking for. Again thanks.

herilane

Hmmm... the formula looks all right, but doesn't work for me either.

Here's a workaround... a bit of a kludge, but works. Again, Ctrl+Shift+Enter to enter it.
=IF(SUM(NOT(ISNUMBER(A1:J1))*1)>0,"error","ok")

Kluz

Umm... what iserror formula?
That's weird, it works once or twice then stops working. Herilane's works fine though and still works even if it's not an array formula. Still trying to wrap my head around why you would need to multiply 1 Last time I checked multiplying anything by 1 results in the same value. Thank you Microsoft!

Mike Rosenblum

I'm not that great at this Array Formula stuff, but I'm sure Helen can clarify this tomorrow... but I do know why multiplying by 1 is necessary. It is because the =Sum() function cannot Sum boolean values such as 'True'. But you can coerce the value, using True * 1 = 1 and False * 1 = 0. (In VBA True * 1 = -1, so one has to be careful! ) This "multiply by 1" technique is, more or less, the Excel Worksheet's method of effecting CLng(), if you will...

italkid

An other formula way (not an array one) to achieve it...

Joe100

Thank you all. I haven't tried them yet but I feel sure that Herilane's and Talkids solutions will work. Thanks to Mike for telling us why you need to multiply by 1 in Herilane's formula. I was curious about that myself.

Kluz

Thanks for that clarification Mike.