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

[jerickson3]

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!

 

[Juan Pablo González]

Well, the solution is

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

 

[jerickson3]

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)

Your example: IF(TheNumber<35,TheNumber,MROUND(TheNumber,5)

Thanks again!

 

[Juan Pablo González]

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

 

[Pekkavee]

Re: 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)

Your example: IF(TheNumber<35,TheNumber,MROUND(TheNumber,5)

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