Forum Discussion
Round up / down in calculated column
Hi I am trying to use a calculated column to round up or downs cash figures. I can achieve this easily in excel, I thought the formula would be similar just formatted to work with list column...
Thank you for this - I think something similar to this will work for the cash rounding column, but not for the voucher rounding column and vouchers are only provided in multiples of 5, not the next whole number...
Here is what it's doing currently compared to the excel version
ok, to desired rounding behavior for both the cash rounding and voucher rounding columns in your list, you may need to use a combination of calculated columns and custom formulas. While Excel provides more flexibility in this regard, you can still approximate the functionality in SharePoint by following these steps:
Create a new calculated column for the cash rounding. Let's call it "Cash Rounding."
Use the following formula in the calculated column:
=IF(MOD([Total], 1) > 0.5, ROUNDUP([Total], 0), ROUNDDOWN([Total], 0))
This formula checks if the decimal part of the [Total] value is greater than 0.5. If it is, the [Total] value is rounded up using the ROUNDUP function; otherwise, it is rounded down using the ROUNDDOWN function.
Create another calculated column for the voucher rounding. Let's name it "Voucher Rounding."
Use the following formula in the calculated column:
=ROUND([Total] * (2/3), 0) * 5
This formula multiplies the [Total] value by 2/3 to obtain two-thirds of the total. The result is then rounded to the nearest whole number using the ROUND function. Finally, the rounded value is multiplied by 5 to ensure that it aligns with the voucher requirement of multiples of 5.
By implementing these calculated columns, you can approximate the rounding behavior you described in the Excel version. However, it's important to note that SharePoint's calculated columns have limitations compared to Excel's formulas, and the precision of calculations may vary. It's recommended to thoroughly test and validate the results to ensure they meet your requirements.
Also, keep in mind that SharePoint lists are not as flexible as Excel in terms of complex calculations, and you may encounter some limitations when trying to replicate certain functionalities.
Create a new calculated column for the cash rounding. Let's call it "Cash Rounding."
Use the following formula in the calculated column:
=IF(MOD([Total], 1) > 0.5, ROUNDUP([Total], 0), ROUNDDOWN([Total], 0))
This formula checks if the decimal part of the [Total] value is greater than 0.5. If it is, the [Total] value is rounded up using the ROUNDUP function; otherwise, it is rounded down using the ROUNDDOWN function.
Create another calculated column for the voucher rounding. Let's name it "Voucher Rounding."
Use the following formula in the calculated column:
=ROUND([Total] * (2/3), 0) * 5
This formula multiplies the [Total] value by 2/3 to obtain two-thirds of the total. The result is then rounded to the nearest whole number using the ROUND function. Finally, the rounded value is multiplied by 5 to ensure that it aligns with the voucher requirement of multiples of 5.
By implementing these calculated columns, you can approximate the rounding behavior you described in the Excel version. However, it's important to note that SharePoint's calculated columns have limitations compared to Excel's formulas, and the precision of calculations may vary. It's recommended to thoroughly test and validate the results to ensure they meet your requirements.
Also, keep in mind that SharePoint lists are not as flexible as Excel in terms of complex calculations, and you may encounter some limitations when trying to replicate certain functionalities.
- oh, so sorry about the result.