Share this content
0
564

Mod for variable decimals in excel

I already have a formula that populates this column. How do I add the Mod. to my formula

I think I understand the Mod potential but my problem is I already have a formula in the cells that I would like to use from 1 to 3 decimal places. The column I would like to have this affect is based on two other columns. My column uses 0.5,0.125,.0.75 and so forth. How would I add this mod to my existing formula for variable decimal places?
=IF(SUM(K9,(L9="f")*0.125,(L9="s")*0.125)=0,"",SUM(K9,(L9="f")*0.125,(L9="s")*0.125))

Replies

Please login or register to join the discussion.

Aug 22nd 2017 16:17

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.

Thanks (0)
avatar
to David Ringstrom
Aug 22nd 2017 18:20

I have a lengthy explanation and thought my first question would have covered it but I did not explain properly. I have created an insulin log. Three of the columns deal with the amount of a dose. Col K Data is the original dose, lets say 0.5IU. Col L data is extra minute dose added and usually is 0.125IU. The (s )in Col L stands for skinny which means the orig dose of 0.5 less 0.125. The (f) in Col L stands for fat which means the orig dose plus 0.125 more. Col N sums the total dose of Cols K&L which can yield from one to three decimal places. These are the original dosing amounts to deal with. 0.5,0.75, 1.0,0.150. This is why I was looking for away to have Col N to be able to keep precise numbers when the dosages can use from one to three decimal places.

Thanks (0)
Aug 22nd 2017 19:30

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.

Thanks (0)
avatar
Aug 22nd 2017 23:30

Alright I see what your saying with the formula you said would work. The problem I have with either one is the rounding. I know it seems minor but to other it can be confusing for them when precise dosing is involved and that is what I have been trying to solve which may not be possible? I am new at this so please forgive me. If a person gives 1.0IU and gives 0.125 more(which is considered a fat(f) then that one unit becomes 1.125. but if I type in S for skinny which would be 0.125 less of the 1UI it adds the 0.125 So the formula you sent me is reversed., so I reversed the f&s.The (f)fat subtracts the 0.125 and the (s)skinny adds 0.125. The fat should add 0.125 and the skinny should subtract the 0.125 from the main dose. Now if I just dose a "s" or "f" which would be 0.125 it does not populate the N column with the 0.125. Am I confusing you, I hope not.

Thanks (0)
Aug 23rd 2017 17:48

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.

Thanks (0)
avatar
Aug 23rd 2017 19:27

Thankyou. I have insert the new formula, but I am back to my original question. In order to keep the sum of K & L columns consistent and maybe I am being to much of a perfectionist as for excel. The 0.75, 0.5, 0.125 and the 1.0 plus (s) comes out perfect. My question would be is it possible when I type in 1.0 dose or 2.0 dose without a fat or skinny the sum does not come with 1.0 or 2.0. It sums with 1 or two and does not add that .0 I suppose that is the limitation of excel.
Col K
0.75
1.0 s

0.75

1.0

2.0

Col N

0.625

0.75
0.875

0.75

1

2

Thanks (0)
to Old Cat Woman
Aug 23rd 2017 20:14

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

Thanks (0)
avatar
to David Ringstrom
Aug 23rd 2017 21:06

Alright I followed your instructions and see what you are explaining. I have come to a conclusion that what I am asking may be an impossibility in excel. All the numbers except for the 1.0 or 2.0 or any numbers with a trailing zero do not replicate from Col K(trailing zero) to Col N but populate without the trailing 0. That is why I ask about a VBA that would populate Col N the same as Col K and yet use the same formula for sum up. That would give me the precision I am looking for. If Col K has 2.0 with a trailing 0 I would like Col N to be 2.0 with a trailing 0 without changing the rest of the cells with different numbers. I hope I am not straining your patience with me.

Thanks (0)
to Old Cat Woman
Aug 23rd 2017 21:20

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.

Thanks (0)
avatar
to David Ringstrom
Aug 23rd 2017 21:49

I there and as for choosing format I suppose I should be choosing format cells for General or numbers?

Thanks (0)
to Old Cat Woman
Aug 24th 2017 00:09

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

Thanks (0)
avatar
to David Ringstrom
Aug 24th 2017 00:41

Perfect. I have been asking this question for a long time and finally have the answer. Thank you for you patience and kindness.

Thanks (0)
to Old Cat Woman
Aug 24th 2017 13:10

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

Thanks (0)
avatar
Sep 11th 2017 07:39

David, The conditional formatting worked well for Column N Total dose for 76 cells, then I started having problems getting the VBA to run. In order to run the VBA I have to manually run it. If I read and understand that in the user cells I cannot use this conditional formatting in unlocked cells. How can I use this same principal for the user cells which are in Column K without losing a formula for the decimal places.
Also the formula you suggested for N column for the additional 0.125 is a constant addition of the fats and skinny's which is not always used with each dose. It can be used by itself or added to a dose when a titch more insulin would be needed.

Thanks (0)
Share this content