• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF Statement Returning Incorrect Statement

C

Chris

New Member
I have the following formula;

=IF(AND(C14>"1000",C14<"1405"),CONCATENATE("out of tol on the following blade ",'EQN Data - Inspection'!F14," ",'EQN Data - Inspection'!G14," ",'EQN Data - Inspection'!H14," ",'EQN Data - Inspection'!I14),CONCATENATE('EQN Data - Inspection'!F14," ",'EQN Data - Inspection'!G14," ",'EQN Data - Inspection'!H14," ",'EQN Data - Inspection'!I14))

The problem is that C14 can contain numbers (i.e. 1-2000) and a combination of numbers and letters (i.e. 1391.1c), and in any order dependant upon the errors that have occurred.

There seems to be an issue when it come to the cell containing 10-14 and 100-140. These values return the statement "out of tolerance", almost as if it thinks these numbers are 1000-1400 and hence falling within the criteria.

The cells are currently formatted as text due to those that contain the mix of numbers and letters and appears to work on most other cells but cannot get it to work on all cells within the whole range.

Any help would be great!
 
For example;

If I were to type 11 into C14 or any of the other cells, it return the "out of tol on the following blade " statement. Even though this should only appear when a value between 1000 and 1405 is entered.

Hope this helps.
 
Hi Chris ,

Can you try this array formula , to be entered using CTRL SHIFT ENTER ?

=CONCATENATE(IF(OR(ISNUMBER(SEARCH(ROW(1000:1405),C14))),"out of tol on the following blade ",""),'EQN Data - Inspection'!F14," ",'EQN Data - Inspection'!G14," ",'EQN Data - Inspection'!H14," ",'EQN Data - Inspection'!I14)

Narayan
 
This has worked for most of the cells now, thank you.

There is still an issue when the cell contained 1391.1c for example though.

The letter has to be there in order to distinguish between 1391.1c and 1391.1v for example.

Can anything be done about this?
 
Hi Chris ,

What do you want that the formula should return if a cell contains 1391.1c , and what should happen if it contains 1391.1v ?

Narayan
 
In this instance i would like it to recognise the number for what it is (i.e. 1391.1) but then it is important that the c or v for example is still there in order to pick up the correct wording in the rest of the statement.
hopefully i have explained that clearly enough.
 
Hi Chris ,

I have still not understood the problem ; your first post contained a formula , where your problem was this :

There seems to be an issue when it come to the cell containing 10-14 and 100-140. These values return the statement "out of tolerance", almost as if it thinks these numbers are 1000-1400 and hence falling within the criteria.
Click to expand...

The formula you posted , and the formula I have posted are almost identical in the action they take ; I am not able to understand how your original formula preserved the c and the v ; the formula I posted does not do anything to the original contents of C14 ; they will remain as they are.

Probably if you upload your workbook , I can get a better idea of your problem.

Narayan
 
The cells containing c and v worked previously with the original formula due to the quotation marks being around the parameter values (1000-1405) and them being formatted as a Text cell.

I cannot necessarily explain why this worked but it appeared to. However this has now changed, although the original problem of the pure number cells appears all to be working.
 
Hi Chris ,

I understand how your original formula worked with values like 1391.1c and 1391.1v ; if these values were in C14 , your IF statement was comparing C14 with two other text strings viz. "1000" and "1405" ; a text string such as 1391.1c would fall in between "1000" and "1405".

What I don't understand is why you want the formula to distinguish between 1391.1c and 1391.1v ; your original formula was using C14 only to test ; the output was not using C14 ; the output was using only F14 , G14 , H14 and I14. The formula I have posted does exactly the same ; so what is the problem ?

Is it possible you can upload your file , since otherwise we will continue to exchange posts without going forward.

Narayan
 
It doesn't necessarily need to differentiate between the c and the v in this cell.
But the problem is that the current formula you posted does not seem to recognise that 1391.1c falls in the range between 1000 and 1405, and therefore does not return the correct statement "out of tol on the following blade".
It either returns #N/A or ""
 
Hi Chris ,

I don't think so ; what the IF statement is doing is looking for the numbers from 1000 through 1405 in the contents of C14 , which can be just numeric , or can be text as in 1391.1c ; the number 1391 will be found in this text , and the result should be the text "out of tolerance".

I have tried it in a worksheet , and it works.

Narayan
 
Would I be able to see this in your worksheet?

I have just tried it myself and it doesn't seem to work, and have attached this. (I realise the formula is different but the concept is the same in what it is trying to achieve)

Thanks
 

Attachments

  • Excel Test.xlsx
    9.1 KB · Views: 1
Hi Chris ,

Can you check the file now ?

I have used the $ sign for 1000 and 1405 since the formula has to be copied down.

Narayan
 

Attachments

  • Excel Test.xlsx
    8.2 KB · Views: 4
Brilliant! Appears to work.
I have also now realised a personal error in not ensuring it was an array formula all the time either.

I shall test it out and check it over with my colleague on Monday though just to make sure.
 
You must log in or register to reply here.
Back
Top