Excel 2007 : Formula not working and cant see what is wrong

**DvDj** · 02-02-2009, 09:48 AM

Hi Guys,

After some help understanding what i have done wrong with this formula.

=IF(H30>Z62,VLOOKUP(AK60,oddsSods!J2:K21,2,TRUE),VLOOKUP(AK60,[Data.xlsx]code34!A2:B35,2,TRUE))

There seems to be a conflict with the greater than part of the formula. So if greater than; use the first lookup else use the second look up. Every time it uses the second lookup.

Any help much appreciated

DvDj

**arthurbr** · 02-02-2009, 09:52 AM

Are h30 and z62 numbers ?
If so, are they real numbers or text looking like numbers ? ( in the latter case they are left-aligned if no formatting is applied)

**DvDj** · 02-02-2009, 11:14 AM

Hi Arthurbr,

Both cells are formatted as numbers with no decimal places and aligned right

**DonkeyOte** · 02-02-2009, 11:18 AM

Continuing Arthur's point... what happens if you replace:

=IF(H30>Z62

with

=IF(N(H30)>N(Z62),

(point being formatting a number does not alter it's underlying type... so if it was a text value, formatting to number has 0 impact... it must be coerced to numeric type)

**DvDj** · 02-02-2009, 11:27 AM

DonkeyOte,

Many thanks for your help i have tried it and in every case it now only refers to the first look up and now ignors the second!!!

**DonkeyOte** · 02-02-2009, 11:41 AM

I suggest you post a sample of the file with appropriate values in H30 and Z62 such that we can review.... presumably your file is on auto-calculation ?

**DvDj** · 02-10-2009, 10:40 AM

Thank you everyone for helping me with the above issue.

I wasnt able to post an example due to the sensitivity of the data and every time i mocked up a working example the formula worked!!

I have finally however now got to the bottom of it. It turns out that it couldn't work out which value was larger because the data (from another spreadsheet) used to populate the cells were formatted as text. So the original document data was text and in the final spreadsheet that was using this data, although formatted to general couldn't recognise it as anything other than text, so failing the formula.

Reformatted the original data and it all works fine now.

Again many thanks for your help and support.

**DonkeyOte** · 02-10-2009, 10:44 AM

My bad in hindsight... this:

=IF(N(H30)>N(Z62),

should in fact have been

=IF(VALUE(H30)>VALUE(Z62),

at which point it would have worked but revising the underlying data is best when possible