Forum Discussion
Rounding in XLOOKUP
Hello All,
New user to this group. I am trying to Round down to the nearest 100 (1,126 should round to 1,100) incorporated into XLOOKUP. Having a hell of time doing this. keeps giving me errors.
=ROUNDDOWN(XLOOKUP(A32,'Recharge_Analysis-New'!A6:A17,'Recharge_Analysis-New'!C6:C17,"ERROR",0)
Can some one shed some light on this please.
Thanks
=ROUNDDOWN(XLOOKUP(A32,'Recharge_Analysis-New'!A6:A17,'Recharge_Analysis-New'!C6:C17,"ERROR",0),-2)
or
=FLOOR.MATH(XLOOKUP(A32,'Recharge_Analysis-New'!A6:A17,'Recharge_Analysis-New'!C6:C17,"ERROR",0),100)
4 Replies
=ROUNDDOWN(XLOOKUP(A32,'Recharge_Analysis-New'!A6:A17,'Recharge_Analysis-New'!C6:C17,"ERROR",0),-2)
or
=FLOOR.MATH(XLOOKUP(A32,'Recharge_Analysis-New'!A6:A17,'Recharge_Analysis-New'!C6:C17,"ERROR",0),100)
- Hans,
Thank you very much, this is great worked like a charm......
Hay I am not too familiar with FLOOR.MATH. Can explain what is the difference and in your opinion when one should use one Vs the other.
ArdyROUNDDOWN rounds towards zero to a specified number of decimal places; if the number of decial places is negative, it rounds towards zero to a multiple of a power of 10.
FLOOR.MATH rounds towards minus infinity to a multiple of a specified number.
Perhaps the following examples will make it a bit clearer:
ROUNDDOWN(234.678, 1) returns 234.6
ROUNDDOWN(234.678, -2) returns 200 (a multiple of 10^2)
ROUNDDOWN(-234.678, 1) returns -234.6
ROUNDDOWN(-234.678, -2) returns -200 (a multiple of 10^2)
FLOOR.MATH(234.678, 0.1) returns 234.6 just like the ROUNDDOWN example
FLOOR.MATH(234,678, 70) returns 210 (the next lower multiple of 70)
FLOOR.MATH(-234.678, 0.1) returns -234.7 - this is different from the ROUNDDOWN example
FLOOR.MATH(-234,678, 70) returns -280 (the next lower multiple of 70 towards minus infinity)
As long as your numbers are greater than or equal to zero, ROUNDDOWN(..., -2) and FLOOR.MATH(..., 100) will return exactly the same values. So you can use whichever one takes your fancy.
But for negative numbers, the result will be different. So you'll have to decide which of the two does what you prefer.
