FORMULA ASSISTANCE
Hi Experts, Id like a solution to solve the following error i have in my formula
FORMULA
[qty*1.1rnd0.3/4.2rnd1] /4.2
What Im wanting to do here is, whatever qty i add, i need the formula to add 10%, then round up to the next increment of 0.30, then i want it to divide by 4.20 and round up to the next full 4.20, the result to have 4.2 shown
If I enter the qty as 1.80 and have the formula work correctly, it should give me the following result 1/ 4.20
Here are the calculation steps that should be taken
1.80x1.1=1.98
1.98 roundup into increments of 0.3 = 2.10
2.10 / 4.20 = 0.50
0.50 roundup into increments of 4.20 = 4.20
final result = 1/ 4.20
FORMULA
[qty*1.1rnd0.3/4.2rnd1] /4.2
What Im wanting to do here is, whatever qty i add, i need the formula to add 10%, then round up to the next increment of 0.30, then i want it to divide by 4.20 and round up to the next full 4.20, the result to have 4.2 shown
If I enter the qty as 1.80 and have the formula work correctly, it should give me the following result 1/ 4.20
Here are the calculation steps that should be taken
1.80x1.1=1.98
1.98 roundup into increments of 0.3 = 2.10
2.10 / 4.20 = 0.50
0.50 roundup into increments of 4.20 = 4.20
final result = 1/ 4.20
Last Comment
I'm assuming that you need to prefix the numeric result with the text "1/"
="1/" & TEXT(ROUNDUP((ROUNDUP(A1*1
Before adding the prefix, it's the same as Saqibh's approach, except that I rounded up to the nearest 0.30 rather than 0.33333333
="1/" & TEXT(ROUNDUP((ROUNDUP(A1*1
Before adding the prefix, it's the same as Saqibh's approach, except that I rounded up to the nearest 0.30 rather than 0.33333333
Hi Brad, I thought mine rounds to 0.30, how do you say it rounds to 0.33333?
Saqib
Saqib
...deleted
Saqib,
You are correct. I was wrong about how the ROUNDUP function is working.
Brad
You are correct. I was wrong about how the ROUNDUP function is working.
Brad
You could use the MROUND function or the CEILING function.
MROUND rounds to the nearest multiple specified
CEILING rounds up to the next multiple specified.
Thanks
Rob H
MROUND rounds to the nearest multiple specified
CEILING rounds up to the next multiple specified.
Thanks
Rob H
With quantity in A2, use formula:
=TEXT("1/ "&CEILING(CEILING(A2*1.1,0
Thanks
=TEXT("1/ "&CEILING(CEILING(A2*1.1,0
Thanks
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Or
="1/ "&TEXT(CEILING(CEILING(A2*
Thanks
Rob H
="1/ "&TEXT(CEILING(CEILING(A2*
Thanks
Rob H
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
=ROUNDUP(ROUNDUP(A1*1.1/3,