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

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

joeu2004

Well-known Member
=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)

richelg

New Member
t
=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)
this is actually my spreadsheet, so in the C/limit calc - I want the total to be automatically round up

 Media Type Approx Mntly Expenditure % of mnthly Expenditure C/limit calc - based on 6mnths round up to nrst 10,000 - place limit in booking system Nine Tv Metro 500,000​ 0.7610​ 3,805,175.04​ \$ 3,810,000 Nine TV Darwin 1,000​ 0.0015​ 7,610.35​ \$ 10,000 Nine NBNE 5,000​ 0.0076​ 38,051.75​ \$ 40,000 Nine Radio 50,000​ 0.0761​ 380,517.50​ \$ 390,000 Nine Digital 50,000​ 0.0761​ 380,517.50​ \$ 390,000 Nine Publishing 50,000​ 0.0761​ 380,517.50​ \$ 390,000 Production 1,000​ 0.0015​ 7,610.35​ \$ 10,000 Total Annual Expenditure 657,000​ 5,000,000.00​ \$ 5,040,000 Credit Limit 6mnths 3,942,000.00​ Tier table to be hidden in background Tier Credit Limit Approval Approval Lvl Tier 1 {\$1 - \$10,000} 10,000.00​ C/officer Tier 2 {>\$10,000 - \$50,000} 50,000.00​ C/officer Tier 3 {>\$50,000 - \$100,000} 100,000.00​ C/officer Tier 4 {>\$100,000 - \$500,000} 500,000.00​ C/Lead Tier 5 {>\$500,000 - \$1,000,000} 1,000,000.00​ C/Mngr Tier 6 {\$1,000,001 - \$5,000,000} 5,000,000.00​ Danielle - Finance Ops Tier 7 >\$5,000,001 90,000,000.00​ Graeme - CFO

joeu2004

Well-known Member
=ROUNDUP(the C/limit calc, -4)

richelg

New Member

=ROUNDUP(the C/limit calc, -4)
is there a possibility that the answer in column 4 will automatically round up rather than for me adding up a column and do the round up

joeu2004

Well-known Member
is there a possibility that the answer in column 4 will automatically round up rather than for me adding up a column and do the round up

No.

Well, "any possibility". Yes: a VBA event macro. I don't recommend it. And I will not pursue that any further here.

In the future, please post your formulas. I had hoped that you would get that from my terse responses. But I guess they were too subtle.

richelg

New Member

No.

Well, "any possibility". Yes: a VBA event macro. I don't recommend it. And I will not pursue that any further here.

In the future, please post your formulas. I had hoped that you would get that from my terse responses. But I guess they were too subtle.
Thanks joeu2004, actually I figured it out, I wasbable to get the correct formula, thanks for your help

joeu2004

Well-known Member
in the C/limit calc - I want the total to be automatically round up
is there a possibility that the answer in column 4 will automatically round up rather than for me adding up a column and do the round up

I might have misunderstood your objectives. I thought you wanted the column labeled "C/limit calc" to look like the column labeled "round up to nrst 10,000".

If you want to keep the exact values in the column labeled "C/limit calc" (3,805,175.04 etc), and you just want the "Total Annual Expenditure" in D9(?) to be 5,040,000, the sum of the "C/limit calc" values each rounded up to 10,000, you can replace current formula that results in 5,000,000 with:

=SUMPRODUCT(ROUNDUP(D2:D8,-4))

smozgur

BatCoder
@richelg - Welcome to the MrExcel Message Board!

Glad to hear you got the solution.

Do you mind posting your corrected formula if it is something different than the one that joeu2004 posted and I marked as the solution? Then it is perfectly fine to mark your post as the solution to help future readers.

richelg

New Member
@richelg - Welcome to the MrExcel Message Board!

Glad to hear you got the solution.

Do you mind posting your corrected formula if it is something different than the one that joeu2004 posted and I marked as the solution? Then it is perfectly fine to mark your post as the solution to help future readers.
no problem

=CEILING((D3*VLOOKUP(\$C\$14,\$C\$26:\$D\$33,2,FALSE)),10000)

Replies
2
Views
42
Replies
1
Views
376
Replies
4
Views
100
Replies
4
Views
71
Replies
0
Views
18

1,129,700
Messages
5,637,887
Members
416,988
Latest member
Ahmed_Yehia

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?

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

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