Forum Discussion
Formula in Excel Spreadsheet not work
Hi Sir,
Picture 1 shows the desired condition, where the row of the spreadsheet turns orange when the SPT (N) is more than 50. However, it will not work where the SPT (N) at row 47 become 0. All rows below row 48 become orange colour as picture 2.
https://mega.nz/folder/g5dizZqR#7Pk7eyU0hXZTL6sj3qVJ-g. We attach the file in follows link for your further action.
Thanks.
1 Reply
The use of an approximate match in VLOOKUP requires the lookup range to be sorted in ascending order. Yours is not and the CF formula will never give the correct answer. And when you think it does, that's just a coincidence.
What is the logic for the conditional format? Can you describe it in words?
The value of 50 seems to your breaking point and you want to return the next lowest value. Correct? That would be 22, while your VLOOKUP returns 20. This is due to the fact that VLOOKUP performs a so-called binary search.
