Avatar of Frank .S
Frank .S
Flag for Australia asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Saqib Husain

If A1 contains the qty then enter this formula in another cell and it will give you the desired result (I hope)

=ROUNDUP(ROUNDUP(A1*1.1/3,1)*3/42,1)*42/4.2
byundt

I'm assuming that you need to prefix the numeric result with the text "1/"
="1/" & TEXT(ROUNDUP((ROUNDUP(A1*1.1/0.3,0)*0.3)/4.2,0)*4.2,"#,##0.00")

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
Saqib Husain

Hi Brad, I thought mine rounds to 0.30, how do you say it rounds to 0.33333?

Saqib
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
terencino

...deleted
byundt

Saqib,
You are correct. I was wrong about how the ROUNDUP function is working.

Brad
Rob Henson

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob Henson

With quantity in A2, use formula:

=TEXT("1/ "&CEILING(CEILING(A2*1.1,0.3)/4.2,4.2),"0.00")

Thanks
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob Henson

Or

="1/ "&TEXT(CEILING(CEILING(A2*1.1,0.3)/4.2,4.2),"0.00")

Thanks
Rob H