0

I currently have a spreadsheet that i want an occurance ratio, the lookup value is giving me 9 decimal places, how can i change it to 2 so i don't have a million lines with the lookup values? Example: my occurance ratio is cell AB7, this cell is =S7/AA7 (quantity of rejects divided by parts supplied) this gives a figure of for example 0.001552589. But i have the cell show only 2 decimal places. I want another cell, AE7, to show a single number depending on the result. So i have a list of 7002 numbers, all 2 decimal places, to look for the matching number (Lookup array) and a list of the number to appear in AE7 (Return array) All numbers 2 decimal places or i would need a million lines to search, however, when doing the XLOOKUP it shows the cell = in the 9 decimal places, I have ensured that all cells with any numbers all show 2 decimal places, but i constantly get an error as it is looking for the exact number to 9 decimal places.

How do i fix this please?

enter image description here

Improve this question

1 Answer 1

Reset to default
0

=ROUND(S7/AA7, 2)

That rounds the number before display, lookup etc. Rather than setting the display format which only rounds it for display.

Improve this answer
1
  • Ah thank you so much, I never knew about '=ROUND' that has worked perfectly. Thank you :-)
    – Tracy Martin
    Commented May 21 at 12:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .