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

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

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

="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

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

...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

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

With quantity in A2, use formula:

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

Thanks

=TEXT("1/ "&CEILING(CEILING(A2*1.1,0

Thanks

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

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
Or

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

Thanks

Rob H

="1/ "&TEXT(CEILING(CEILING(A2*

Thanks

Rob H

=ROUNDUP(ROUNDUP(A1*1.1/3,