Tjyylli

Is it legal for company to use my work email to pretend I still work there?

Important Resources for Dark Age Civilizations?

What would happen to a modern skyscraper if it rains micro blackholes?

Which country benefited the most from UN Security Council vetoes?

Can a Cauchy sequence converge for one metric while not converging for another?

If human space travel is limited by the G force vulnerability, is there a way to counter G forces?

"You are your self first supporter", a more proper way to say it

Did Shadowfax go to Valinor?

How much of data wrangling is a data scientist's job?

Can a monk's single staff be considered dual wielded, as per the Dual Wielder feat?

How bulky would the original autograph of the Torah been?

Why is 150k or 200k jobs considered good when there's 300k+ births a month?

Rock identification in KY

How can bays and straits be determined in a procedurally generated map?

Uncaught TypeError: 'set' on proxy: trap returned falsish for property Name

dbcc cleantable batch size explanation

Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?

Question on branch cuts and branch points

Can the number of solutions to a system of PDEs be bounded using the characteristic variety?

Could an aircraft fly or hover using only jets of compressed air?

Are astronomers waiting to see something in an image from a gravitational lens that they've already seen in an adjacent image?

How to format long polynomial?

Mutually beneficial digestive system symbiotes

Does detail obscure or enhance action?

Excel Formula on Rounding Up or Down

Excel formula to calculate power ofRound up or down to nearest multiple of .05 in ExcelFormula issue Microsoft ExcelExcel 2010 formula?How to use a logical function in a formula as textHow to know for sure if an Excel Function shall work in an Array Formula?Excel: Creating a formula that skips weekendsIs there a concise Excel formula to calculate (A1*A6)+(B1*B6)+(C1*C6)…?Cell joining, bracketing, and rounding in one excel formulaEXCEL SUM Rounding

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

-1

Is there a function where I "dictate" to Excel to help with the following:

A) Round UP to the nearest $0.10 cent if it is > $0.08
$15.78 -> $15.80

B) Round DOWN to the nearest $0.10 if it is < $0.07
$15.77 -> $15.70

Is there a formula that considers both A & B?

Thanks in advance.

microsoft-excel worksheet-function

share|improve this question

asked 16 hours ago

AutumnAutumn

1

New contributor

Autumn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why don't you share some sample data to test and fix the issue since the B part of your question doesn't needs to test with IF and part A does need, in this situation any common formula can't works properly!!
  
  – Rajesh S
  14 hours ago
  
  
  

  
  
  
  

add a comment | 

-1

Is there a function where I "dictate" to Excel to help with the following:

A) Round UP to the nearest $0.10 cent if it is > $0.08
$15.78 -> $15.80

B) Round DOWN to the nearest $0.10 if it is < $0.07
$15.77 -> $15.70

Is there a formula that considers both A & B?

Thanks in advance.

microsoft-excel worksheet-function

share|improve this question

asked 16 hours ago

AutumnAutumn

1

New contributor

Autumn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why don't you share some sample data to test and fix the issue since the B part of your question doesn't needs to test with IF and part A does need, in this situation any common formula can't works properly!!
  
  – Rajesh S
  14 hours ago
  
  
  

  
  
  
  

add a comment | 

Is there a function where I "dictate" to Excel to help with the following:

A) Round UP to the nearest $0.10 cent if it is > $0.08
$15.78 -> $15.80

B) Round DOWN to the nearest $0.10 if it is < $0.07
$15.77 -> $15.70

Is there a formula that considers both A & B?

Thanks in advance.

microsoft-excel worksheet-function

share|improve this question

asked 16 hours ago

AutumnAutumn

1

New contributor

Autumn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 16 hours ago

AutumnAutumn

1

New contributor

Autumn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 16 hours ago

AutumnAutumn

1

New contributor

Autumn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 16 hours ago

AutumnAutumn

1

New contributor

Autumn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 16 hours ago

AutumnAutumn

1

2 Answers
2

active

oldest

votes

0

You can write one! The function you describe can be built out of these elements:

• IF function


• ROUNDUP and ROUNDDOWN functions

So you can build your function like so (for an example cell A1):

= IF(A1 >= 0.08,        # the if condition

     ROUNDUP(A1, 1),    # case if true: round up to the nearest 1st decimal place

     ROUNDDOWN(A1, 1)   # case if false: round down to the nearest 1st decimal place

    )

Note that I've assumed that when you said > $0.08 and < $0.07, you actually meant ≥ and ≤, because otherwise the inputs of 7¢ and 8¢ themselves would have no outputs in your function :)

share|improve this answer

answered 15 hours ago

Niayesh IskyNiayesh Isky

290311

add a comment | 

0

In single formula form:

B1=ROUND(A1-0.025,1)

---

Or the same in user-defined function form:

Public Function MyRound(sum As Currency) As Currency

MyRound = Round(sum - 0.025, 1)

End Function

Put this code into a common module, than use this funcion in a cell:

B1=MyRound(A1)

---

In both cases - to view 2 digits after decimal point format the cell properly. If not, trailing zero will be truncated.

share|improve this answer

edited 15 hours ago

answered 15 hours ago

AkinaAkina

1,36929

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  OP needs to test both value with >0.08 and <0.07 before Round to nearest value, in this case ROUN DOWN never works since value 15.77 is not <0.07 , will set to the FALSE part !!
  
  – Rajesh S
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS I cannot understand you. OP says: $15.77 -> $15.70 and $15.78 -> $15.80 My formulas gives this results exactly. What's wrong?
  
  – Akina
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Read OP,, Round UP to the nearest $0.10 cent if it is > $0.08,, $15.78 -> $15.80, means Roundup 15.78 to 15.80,, if 15.78 is >0.08 ,, And so that for Part B also !!
  
  – Rajesh S
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well. Look at the formula. If A1=$15.78, then A1-0.025 gives $15.755, and ROUND($15.755,1) gives $15.80. If A1=$15.77, then A1-0.025 gives $15.745, and ROUND($15.745,1) gives $15.70.
  
  – Akina
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And what about this if it is > $0.08 and < $0.07 !!
  
  – Rajesh S
  13 hours ago
  

  
  
  
  

 | 
show 2 more comments

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Autumn is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1421671%2fexcel-formula-on-rounding-up-or-down%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

You can write one! The function you describe can be built out of these elements:

• IF function


• ROUNDUP and ROUNDDOWN functions

So you can build your function like so (for an example cell A1):

= IF(A1 >= 0.08,        # the if condition

     ROUNDUP(A1, 1),    # case if true: round up to the nearest 1st decimal place

     ROUNDDOWN(A1, 1)   # case if false: round down to the nearest 1st decimal place

    )

Note that I've assumed that when you said > $0.08 and < $0.07, you actually meant ≥ and ≤, because otherwise the inputs of 7¢ and 8¢ themselves would have no outputs in your function :)

share|improve this answer

answered 15 hours ago

Niayesh IskyNiayesh Isky

290311

add a comment | 

0

You can write one! The function you describe can be built out of these elements:

• IF function


• ROUNDUP and ROUNDDOWN functions

So you can build your function like so (for an example cell A1):

= IF(A1 >= 0.08,        # the if condition

     ROUNDUP(A1, 1),    # case if true: round up to the nearest 1st decimal place

     ROUNDDOWN(A1, 1)   # case if false: round down to the nearest 1st decimal place

    )

Note that I've assumed that when you said > $0.08 and < $0.07, you actually meant ≥ and ≤, because otherwise the inputs of 7¢ and 8¢ themselves would have no outputs in your function :)

share|improve this answer

answered 15 hours ago

Niayesh IskyNiayesh Isky

290311

add a comment | 

You can write one! The function you describe can be built out of these elements:

• IF function


• ROUNDUP and ROUNDDOWN functions

So you can build your function like so (for an example cell A1):

= IF(A1 >= 0.08,        # the if condition

     ROUNDUP(A1, 1),    # case if true: round up to the nearest 1st decimal place

     ROUNDDOWN(A1, 1)   # case if false: round down to the nearest 1st decimal place

    )

Note that I've assumed that when you said > $0.08 and < $0.07, you actually meant ≥ and ≤, because otherwise the inputs of 7¢ and 8¢ themselves would have no outputs in your function :)

share|improve this answer

answered 15 hours ago

Niayesh IskyNiayesh Isky

290311

= IF(A1 >= 0.08,        # the if condition

     ROUNDUP(A1, 1),    # case if true: round up to the nearest 1st decimal place

     ROUNDDOWN(A1, 1)   # case if false: round down to the nearest 1st decimal place

    )

share|improve this answer

answered 15 hours ago

Niayesh IskyNiayesh Isky

290311

answered 15 hours ago

Niayesh IskyNiayesh Isky

290311

answered 15 hours ago

Niayesh IskyNiayesh Isky

290311

0

In single formula form:

B1=ROUND(A1-0.025,1)

---

Or the same in user-defined function form:

Public Function MyRound(sum As Currency) As Currency

MyRound = Round(sum - 0.025, 1)

End Function

Put this code into a common module, than use this funcion in a cell:

B1=MyRound(A1)

---

In both cases - to view 2 digits after decimal point format the cell properly. If not, trailing zero will be truncated.

share|improve this answer

edited 15 hours ago

answered 15 hours ago

AkinaAkina

1,36929

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  OP needs to test both value with >0.08 and <0.07 before Round to nearest value, in this case ROUN DOWN never works since value 15.77 is not <0.07 , will set to the FALSE part !!
  
  – Rajesh S
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS I cannot understand you. OP says: $15.77 -> $15.70 and $15.78 -> $15.80 My formulas gives this results exactly. What's wrong?
  
  – Akina
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Read OP,, Round UP to the nearest $0.10 cent if it is > $0.08,, $15.78 -> $15.80, means Roundup 15.78 to 15.80,, if 15.78 is >0.08 ,, And so that for Part B also !!
  
  – Rajesh S
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well. Look at the formula. If A1=$15.78, then A1-0.025 gives $15.755, and ROUND($15.755,1) gives $15.80. If A1=$15.77, then A1-0.025 gives $15.745, and ROUND($15.745,1) gives $15.70.
  
  – Akina
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And what about this if it is > $0.08 and < $0.07 !!
  
  – Rajesh S
  13 hours ago
  

  
  
  
  

 | 
show 2 more comments

0

In single formula form:

B1=ROUND(A1-0.025,1)

---

Or the same in user-defined function form:

Public Function MyRound(sum As Currency) As Currency

MyRound = Round(sum - 0.025, 1)

End Function

Put this code into a common module, than use this funcion in a cell:

B1=MyRound(A1)

---

In both cases - to view 2 digits after decimal point format the cell properly. If not, trailing zero will be truncated.

share|improve this answer

edited 15 hours ago

answered 15 hours ago

AkinaAkina

1,36929

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  OP needs to test both value with >0.08 and <0.07 before Round to nearest value, in this case ROUN DOWN never works since value 15.77 is not <0.07 , will set to the FALSE part !!
  
  – Rajesh S
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS I cannot understand you. OP says: $15.77 -> $15.70 and $15.78 -> $15.80 My formulas gives this results exactly. What's wrong?
  
  – Akina
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Read OP,, Round UP to the nearest $0.10 cent if it is > $0.08,, $15.78 -> $15.80, means Roundup 15.78 to 15.80,, if 15.78 is >0.08 ,, And so that for Part B also !!
  
  – Rajesh S
  14 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well. Look at the formula. If A1=$15.78, then A1-0.025 gives $15.755, and ROUND($15.755,1) gives $15.80. If A1=$15.77, then A1-0.025 gives $15.745, and ROUND($15.745,1) gives $15.70.
  
  – Akina
  13 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And what about this if it is > $0.08 and < $0.07 !!
  
  – Rajesh S
  13 hours ago
  

  
  
  
  

 | 
show 2 more comments

In single formula form:

B1=ROUND(A1-0.025,1)

---

Or the same in user-defined function form:

Public Function MyRound(sum As Currency) As Currency

MyRound = Round(sum - 0.025, 1)

End Function

Put this code into a common module, than use this funcion in a cell:

B1=MyRound(A1)

---

In both cases - to view 2 digits after decimal point format the cell properly. If not, trailing zero will be truncated.

share|improve this answer

edited 15 hours ago

answered 15 hours ago

AkinaAkina

1,36929

B1=ROUND(A1-0.025,1)

Public Function MyRound(sum As Currency) As Currency

MyRound = Round(sum - 0.025, 1)

End Function

B1=MyRound(A1)

share|improve this answer

edited 15 hours ago

answered 15 hours ago

AkinaAkina

1,36929

answered 15 hours ago

AkinaAkina

1,36929

answered 15 hours ago

AkinaAkina

1,36929