David Ringstrom

Member Since: Jan 6th 2014
Columnist
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.
Author/Presenter/Spreadsheet Consultant Accounting Advisors, Inc.
My answers
Thank you for your kind feedback! I'm glad that I was able to run this down for you.
If you're using Conditional Formatting and MOD as I've described, then choose Number and set the number of decimals to 1.
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.
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. :-)
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.
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.
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.