MROUND divisable of 5, but only if the # is over a certain #

jerickson3

New Member
I am working on a spreadsheet where I want the answer/sum that comes from my formula to round up to a divisable of 5...but only if the answer/sum is higher than a certain number. Let's say 35. I can do the MROUNDs easily, I just can't figure out how to implement the certain number restriction. I don't want it to round up/down like that if the answer/sum is below 35. The formula to get the answer/sum is already pretty big using IF, LOOKUPS, and ROUNDS...so the simpler the better.

Thanks!

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Well, the solution is

IF(TheNumber < 35, TheNumber, MROUND(TheNumber, 5))

MROUND Example

Thanks for your reply. I understand your formula. I just don't know how to get it to work out in my current formula I've tried and it tells me it is not workable. I copied it below...are you able to show me how to include the example you gave?

IF(INDEX('Network Insertion Grid-GLD'!\$A\$3:\$AV\$98,\$E\$3,HLOOKUP(\$E6,'Network Insertion Grid-GLD'!\$D\$1:\$AV\$2,2,FALSE)+3)="Y",ROUND(VLOOKUP(\$E\$3,'Network Insertion Grid-GLD'!\$A\$3:\$B\$98,2,TRUE)*VLOOKUP(G\$3,DaypartFactor,3,FALSE)*VLOOKUP(\$F6,NetFactor,3,FALSE),0),0)

Thanks again!

I would use a different cell for the new calculation, just to make things a little clear. That formula is big enough IMO.

Re: MROUND Example

jerickson3 said:
Thanks for your reply. I understand your formula. I just don't know how to get it to work out in my current formula I've tried and it tells me it is not workable. I copied it below...are you able to show me how to include the example you gave?

IF(INDEX('Network Insertion Grid-GLD'!\$A\$3:\$AV\$98,\$E\$3,HLOOKUP(\$E6,'Network Insertion Grid-GLD'!\$D\$1:\$AV\$2,2,FALSE)+3)="Y",ROUND(VLOOKUP(\$E\$3,'Network Insertion Grid-GLD'!\$A\$3:\$B\$98,2,TRUE)*VLOOKUP(G\$3,DaypartFactor,3,FALSE)*VLOOKUP(\$F6,NetFactor,3,FALSE),0),0)

Thanks again!

Maybe

IF(INDEX('Network Insertion Grid-GLD'!\$A\$3:\$AV\$98,\$E\$3,HLOOKUP(\$E6,'Network Insertion Grid-GLD'!\$D\$1:\$AV\$2,2,FALSE)+3)="Y",IF(ROUND(VLOOKUP(\$E\$3,'Network Insertion Grid-GLD'!\$A\$3:\$B\$98,2,TRUE)*VLOOKUP(G\$3,DaypartFactor,3,FALSE)*VLOOKUP(\$F6,NetFactor,3,FALSE),0),0)>35,ROUND(VLOOKUP(\$E\$3,'Network Insertion Grid-GLD'!\$A\$3:\$B\$98,2,TRUE)*VLOOKUP(G\$3,DaypartFactor,3,FALSE)*VLOOKUP(\$F6,NetFactor,3,FALSE),0),0);VLOOKUP(\$E\$3,'Network Insertion Grid-GLD'!\$A\$3:\$B\$98,2,TRUE)*VLOOKUP(G\$3,DaypartFactor,3,FALSE)*VLOOKUP(\$F6,NetFactor,3,FALSE),0)

Pekka

Replies
3
Views
218
Replies
1
Views
139
Replies
0
Views
253
Replies
3
Views
140
Replies
1
Views
364

1,202,911
Messages
6,052,517
Members
444,588
Latest member
ViJN

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.

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