LoginSign up
AccountingWEB
Subscribe to Newsletter
Menu
Tax
Sales TaxIRSIndividualsBusiness Tax
Practice
ClientsGrowthPractice Strategies
A&A
StandardsFraudAuditing
Technology
ExcelSoftware NewsTrends
Bookkeeping Essentials
White Papers
Blog
Ask AccountingWEB
Subscribe to Newsletter
David Ringstrom, CPA
David Ringstrom
Member Since: Jan 6th 2014
Columnist
Twitter
LinkedIn
Likes: 0
Thanks: 1

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

David Ringstrom, CPA
  • Articles
  • Columnist Posts
  • Blog posts
  • Questions
  • Answers
  • Comments

My answers

Aug 24th 2017

Thank you for your kind feedback! I'm glad that I was able to run this down for you.

Reply to
Mod for variable decimals in excel
Aug 24th 2017

If you're using Conditional Formatting and MOD as I've described, then choose Number and set the number of decimals to 1.

Reply to
Mod for variable decimals in excel
Aug 23rd 2017

I think I see how you settled on the MOD function. You may have run across this article that I wrote:

https://www.accountingweb.com/technology/excel/conditionally-displaying-...

You don't need VBA to do this. If I can restate what you want, if the value is a whole number, you want it a single trailing zero after the decimal. If there is any amount to the right of the decimal, you want to display it without any zeros.

So, a result of 1 will show as 1.0, a result of 0.875 will show as 0.875.

If I have this correct, then the article I referenced above shows how you can use Conditional Formatting to do this. The formula you'll use inside Conditional Formatting is:

=MOD(N9,1)=0

Let me know if between this and the article you can get there.

Don't worry, I spend most of my day in Excel, either teaching live webinars, writing articles, or doing consulting work. It helps me in those endeavors to see examples such as this.

Reply to
Mod for variable decimals in excel
Aug 23rd 2017

We're still circling around the same issue here, which is that you have a number format applied to those cells. :-)

Let me offer a different approach. Select the cells that contain numbers, and then on Excel's Home menu look for the Clear command in the Editing section at the right-hand side. Within the Clear menu choose Clear Formats. I think you'll find that the trailing zeros vanish, and you get to see the full 3 decimals.

Excel is fraught with nuance. :-)

Reply to
Mod for variable decimals in excel
Aug 23rd 2017

1. I follow what you're trying to do. I can tell this is new ground for you, we're all good here.

2. I did inadvertently reverse the formula, but then I corrected it. If you're looking at the email that was sent to you then you saw the incorrect formula. Click the link to view it on this web page and you'll see my correction.

3. There's no actual rounding going in the worksheet cells. As I noted you need to change the number format for the cells to eliminate the perception of rounding. Please look through my instructions again on changing the cell format to General. If those instructions are not clear let me know and I'll spell it out in more detail.

4. Here's the formula again:

=IF(K9=0,"",K9+(IF(L9="s",-0.125,IF(L9="f",0.125,0))))

I copied and pasted it into cell N9 of a blank worksheet. When I enter 1 in cell K9 then the formula returns 1. When I type an "s" in cell L9 then the formula in N9 returns 0.875. If I type an "f" in cell L9 it returns 1.125. I had an extra zero in part of the formula before that may have caused some confusion for you. I've verified the formula above in this post.

Reply to
Mod for variable decimals in excel
Aug 22nd 2017

The additional background is helpful. I think formula accomplishes your goal:

=IF(K9=0,"",K9+(IF(L9="s",-0.125,IF(L9="f",0.0125,0))))

With regard to the decimals, the above formula will show the number of decimals you want if the number format is set to General. I think you may have it set to Number instead which can indeed cause the decimals to be truncated. If you select the cells in column N that contain the formula you use, on Excel's Home menu choose General from the drop down list in the Number section. I think this will get you where you want to be, but let me know if not.

What you have here is a display issue, not a calculation issue, so the MOD function isn't what you need. It's used to show the decimal remainder after using a number in a division calculation.

Reply to
Mod for variable decimals in excel
Aug 22nd 2017

I'm not clear what your goal is here, but it appears that your formula can be shortened to this:

=IF(AND(OR(L9="f",L9="s"),K9=0),"",K9+0.125)

I think that you're trying to add .125 to the value in K9 if that value is not zero and L9 contains either "f" or "s". The MOD function simply returns the value to the right of the decimal, such as

=MOD(IF(AND(OR(L9="f",L9="s"),K9=0),"",K9+0.125),1)

would return 0.125

If you're just trying to determine if 0.125 is needed, then the entire formula can be shorted to this (without the MOD function):

=IF(SUM(K9,(L9="f")*0.125,(L9="s")*0.125)=0,"",0.125)

If I've missed the mark on your goal please elaborate further and I'll be happy to take another shot here.

Reply to
Mod for variable decimals in excel

Most read this week

5 Ways to Duplicate Worksheets in Excel

David Ringstrom
David Ringstrom, CPA
Excel
Aug 18th 2016
Spreadsheets and graphs on a desk

Trending

5 Ways to Duplicate Worksheets in Excel

David Ringstrom
David Ringstrom, CPA
Excel
Aug 18th 2016

What To Do When Worksheet Tabs Go Missing

David Ringstrom
David Ringstrom, CPA
Excel
Sep 20th 2013

Display Times as Hours/Minutes in Excel

David Ringstrom
David Ringstrom, CPA
Excel
Dec 3rd 2014

Upcoming Events

Avalara CRUSH Virtual Event
May 27
More 2021 Event Information Coming Soon
  • About SIFT
  • Advertise on AccountingWEB
  • Terms of use
  • Privacy policy
  • Contact us
Copyright 2021 Sift
Sift