# Combination of IF & Roundup Formula

#### richelg

##### New Member
Hi, I'm trying to combined if formula and roundup for our tiering calculation but I couldn't find the correct formula to get the result I wanted, so if someone can help that will be greatly appreciated
so we have a monthly expenditure and we take the percentage of the monthly expenditure against to the overall total to the the % monthly expenditure and then that percentage and being mutiplied to the tier table and I want the result to be automatically round up rather than adding up another column and using the roundup formula

#### richelg

##### New Member
no problem

=CEILING((D3*VLOOKUP(\$C\$14,\$C\$26:\$D\$33,2,FALSE)),10000)
@smozgur: the above is the easy way to do and below is using by IF formula but it's too lengthy but it works the same way

=IF(AND(\$K\$12<K\$18,\$K\$12>K\$17),K\$18,IF(AND(\$K\$12<K\$19,\$K\$12>K\$18),K\$19,IF(AND(\$K\$12<K\$20,\$K\$12>K\$19),K\$20,IF(AND(\$K\$12<K\$21,\$K\$12>K\$20),K\$21,IF(AND(\$K\$12<K\$22,\$K\$12>K\$21),K\$22,IF(AND(\$K\$12<K\$23,\$K\$12>K\$22),K\$23,"0"))))))*(L2)-4

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### smozgur

##### BatCoder

As joeu2004 also mentioned, next time you might consider providing more information (your formulas, etc.) to get the exact solution. As you can see, @joeu2004's first reply is the correct solution to this question, equivalent to the function you used.

#### richelg

##### New Member

As joeu2004 also mentioned, next time you might consider providing more information (your formulas, etc.) to get the exact solution. As you can see, @joeu2004's first reply is the correct solution to this question, equivalent to the function you used.
@smozgur: and @joeu2004 I was not able to provide the info that I really wanted in the first place that's why she only focused on the rounding up, but all the formulas she has given me did work and I really appreciate it, but I was also trying to incorporate the Tier table in the calculation, basically I want to find to which Tier it falls the total of the credit limit for 6 months then multiply that to the % monthly expenditure then whatever the sum is I want it to be automatically roundup, I'll make sure to put my own formula when I post so everyone can understand what I'm trying to do, it's my first post and I only joined yesterday...

#### smozgur

##### BatCoder
I'll make sure to put my own formula when I post so everyone can understand what I'm trying to do, it's my first post and I only joined yesterday...

Great! I can assure you there are really great experts on the board who are willing to provide amazing solutions when you provide enough information. However, helpers cannot know what you exactly need without knowing the existing structure, and future readers also cannot know what the solution means without knowing the exact question.

I can follow the thread easily and see how joeu2004 explained how you can use the `ROUNDUP` function without knowing your actual formulas.
=ROUNDUP(a monthly expenditure and we take the percentage of the monthly expenditure against to the overall total to the the % monthly expenditure and then that percentage and being mutiplied to the tier table, 0)

Then as soon as you pasted some sample data, without formulas still, provided more information according to your a bit more revealed structure.
=ROUNDUP(the C/limit calc, -4)

Of course, the `the C/limit calc` is your hidden formula in the structure as also explained as "a monthly expenditure and we take the percentage of the monthly expenditure against to the overall total to the the % monthly expenditure and then that percentage and being multiplied to the tier table" in the first reply. So it is just a reference as explained in the first reply.

As you can see, this is equivalent to the following formula:
Excel Formula:
``=CEILING(the C/limit calc,10000)``

When you place the necessary formula as you did in your solution post, you'll get the exact same result.
=CEILING((D3*VLOOKUP(\$C\$14,\$C\$26:\$D\$33,2,FALSE)),10000)

And this has been provided in the second reply. That's why I switched the solution post to provide optimal help to future readers.

#### richelg

##### New Member
Great! I can assure you there are really great experts on the board who are willing to provide amazing solutions when you provide enough information. However, helpers cannot know what you exactly need without knowing the existing structure, and future readers also cannot know what the solution means without knowing the exact question.

I can follow the thread easily and see how joeu2004 explained how you can use the `ROUNDUP` function without knowing your actual formulas.

Then as soon as you pasted some sample data, without formulas still, provided more information according to your a bit more revealed structure.

Of course, the `the C/limit calc` is your hidden formula in the structure as also explained as "a monthly expenditure and we take the percentage of the monthly expenditure against to the overall total to the the % monthly expenditure and then that percentage and being multiplied to the tier table" in the first reply. So it is just a reference as explained in the first reply.

As you can see, this is equivalent to the following formula:
Excel Formula:
``=CEILING(the C/limit calc,10000)``

When you place the necessary formula as you did in your solution post, you'll get the exact same result.

And this has been provided in the second reply. That's why I switched the solution post to provide optimal help to future readers.
@smozgur I think you are still not getting my point, all the formulas given was all about rounding up without incorporating the Tier table which I wanted, so I'm not sure what you are trying to imply here, I felt like you are arguing with me instead of helping, I might be in the wrong forum then......

AND you will not get the same result because if you changed the monthly expenditure it won't incorporate in the Tiering table!

#### smozgur

##### BatCoder
@smozgur I think you are still not getting my point, all the formulas given was all about rounding up without incorporating the Tier table which I wanted, so I'm not sure what you are trying to imply here, I felt like you are arguing with me instead of helping, I might be in the wrong forum then......

I would feel really bad if I offended you somehow as it was definitely not my intention.

I am certainly not arguing with you or implying anything other than trying to explain why I needed to switch the solution post. That's why I asked for your solution to finalize this thread as you mentioned that "it is solved". When I noticed the solution you posted is the same as the previously provided one, by using another function leads to the same result, I marked the closest answer as the solution.

You could still post your worksheet range, preferably by using XL2BB that will automatically include the formulas, then you can still change the marked solution with your own solution as it would surely provide more help to future readers who might have a similar question.

Hope it makes sense.

Replies
2
Views
51
Replies
1
Views
386
Replies
4
Views
110
Replies
4
Views
74
Replies
0
Views
19

1,130,142
Messages
5,640,380
Members
417,139
Latest member
bdmprasenjit

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back