Hi all,
A sheet I receive each month has a long list of data of which I manually change the Price to the nearest 0.25 via MROUND.
Normally I copy all numbers into column D (Workings) and adjust price one-by-one to include the formula MROUND(D6,0.25) and paste back as a value but is there a way to select the relevant cells under QTY and just apply the formula to the value to gain the result quickly?
One thing that is important though is the value needs to return in multiples of 0.25 as the data is used in a calculation afterwards for stock.
Much appreciated - I have included what I have tried below the capture to resolve this.
1. Insert function in the Home ribbon.
2. Find replace the last number 10 times (e.g. find 5 replace with 5, 0.25) and the equivalent at the start but I ended up making errors and it was still slow.
3. Copying all of the QTY into Workings, applying the MROUND formula to the price rows and then copy the column back into QTY, this seems too slow and a problem that shouldn't exist (?).
4. Using VBA is a no-no from my management which may be the deal breaker.
A sheet I receive each month has a long list of data of which I manually change the Price to the nearest 0.25 via MROUND.
Normally I copy all numbers into column D (Workings) and adjust price one-by-one to include the formula MROUND(D6,0.25) and paste back as a value but is there a way to select the relevant cells under QTY and just apply the formula to the value to gain the result quickly?
One thing that is important though is the value needs to return in multiples of 0.25 as the data is used in a calculation afterwards for stock.
Much appreciated - I have included what I have tried below the capture to resolve this.
| Book | Item | Qty | Workings |
| Book 1 | Pages | 300 | |
| Book 1 | Price | 5.57 | 5.50 |
| Book 1 | Genre | Fiction | |
| Book 1 | Genre | Science | |
| Book 2 | Price | 9.99 | 9.99 |
| Book 2 | Genre | Fiction | |
| Book 3 | Price | 1.97 | 1.97 |
| Book 4 | Price | 2.24 | 2.24 |
1. Insert function in the Home ribbon.
2. Find replace the last number 10 times (e.g. find 5 replace with 5, 0.25) and the equivalent at the start but I ended up making errors and it was still slow.
3. Copying all of the QTY into Workings, applying the MROUND formula to the price rows and then copy the column back into QTY, this seems too slow and a problem that shouldn't exist (?).
4. Using VBA is a no-no from my management which may be the deal breaker.
) to get me working better, thanks again.
