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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
=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)
 
Upvote 0
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 TypeApprox Mntly Expenditure% of mnthly ExpenditureC/limit calc - based on 6mnthsround 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
TierCredit Limit ApprovalApproval 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
@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.
 
Upvote 0
@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)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top