Combination of IF & Roundup Formula

richelg

New Member
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,414
Thanks for posting your solution.

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
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Thanks for posting your solution.

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
Joined
Feb 28, 2002
Messages
1,414
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
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Feb 28, 2002
Messages
1,414
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,086
Messages
5,640,014
Members
417,122
Latest member
kirk5370

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top