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

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
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
=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
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
=ROUNDUP(the C/limit calc, -4)
 
Solution

richelg

New Member
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

=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
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Feb 28, 2002
Messages
1,409
@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
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
@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)
 

Watch MrExcel Video

Forum statistics

Threads
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?

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