Calculation not working on formulated cell

[Funkymonkey0073]

Hi All

I need help, on many levels but I'll stick with Excel problem I have.

In a nutshell I have a vlookup formula in cell G51, that result will be either lower or higher than 1.75. In cell H51 I have a formula which SHOULD stay blank if less than 1.75 or say "out of spec" if higher than 1.75 but it doesn't work, or at least is not consistent.

The formula I am using in H51 is: =IF(G51>=1.76,"Out Of Spec","")

but as you will see in the attached example its not working and says out of spec when it is not, I have the same formula in cell H58 which is looking at cell G59 which is giving a different result, i.e. is blank. Both the results of the vlookup in G51 and G59 are formatted as numbers.

I have the same problem in all of the red boxes on the example but If I can solve this I should be able to adapt to work there also.

Any help you can offer would be greatly appreciated, it seems like it should be easy but Im struggling.

Thanks for looking.

[Pete_UK]

Change the formula in G51 to this:

=IFERROR(IF($G11="","",IF(VLOOKUP($G11,Lookup!$A:$AU,45,FALSE)="","",--VLOOKUP($G11,Lookup!$A:$AU,45,FALSE))),"")

The two minus signs in the middle (shown in red) will convert the returned value to a number, rather than the text value that you were returning.

Hope this helps.

Pete

[KOKOSEK]

Value AS25 (0.75) which VLOOKUP gives you in G51 is not a number.
As I see table to lookup is taken from other source and you can''t guarantee that numbers in col. AS will be in proper format so I suggest add 0 (yes, zero) and the end of formula in G51 to be sure that in value in G51 will be 'converted' into number.

[Funkymonkey0073]

Thank you Pete_UK, not just for the solving it but the speed in which you replied, not the first time you have helped me and as always I am very grateful.

Have a great one.

Stuart

[Funkymonkey0073]

Thanks Kokosek, appreciated, I assumed as the cell was formatted as a number the result was.

[Pete_UK]

Glad to help, Stuart, and thanks for the feedback. I was just about to go out and realised that I just had time to respond to your thread.

Pete