Hi, my boss wants me to create a formula for the prices of his products. He wanted all prices below 99.99 to be rounded up to xx.97 and the values from 99.99 and greater to be rounded up to xx.00 and I have managed to put together a round formula like this: "=IF(H1416<99.99,ROUND(H1416,0)-0.03,IF(H1416>=99.99,ROUND(H1416,0)))" and that worked for the decimals.
However, I need to change the last integer before the decimal point while keeping the condition described above.
For all prices below 99.99, the last integer should end in 0, 3, 5, or 9.
For example:
21.97 would be 20.97 since zero is closer than 3, 5, and 9
56.97 would be 55.97 since five is closer than 0, 3, and 9
For all prices from 99.99 and greater, the last integer should end in 0, 3, or 9. (No 5 for this selection)
For example:
174.00 would be 173.00 since three is closer than 0 and 9
I tried using Index and Match but that got me nowhere and adding another condition to the original IF formula posted above, got me with the popup that I had too many arguments. Any ideas on what to do next? I feel like I have reached a roadblock.
However, I need to change the last integer before the decimal point while keeping the condition described above.
For all prices below 99.99, the last integer should end in 0, 3, 5, or 9.
For example:
21.97 would be 20.97 since zero is closer than 3, 5, and 9
56.97 would be 55.97 since five is closer than 0, 3, and 9
For all prices from 99.99 and greater, the last integer should end in 0, 3, or 9. (No 5 for this selection)
For example:
174.00 would be 173.00 since three is closer than 0 and 9
I tried using Index and Match but that got me nowhere and adding another condition to the original IF formula posted above, got me with the popup that I had too many arguments. Any ideas on what to do next? I feel like I have reached a roadblock.